Results 1 to 10 of 14

Thread: SumA22

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1

    SumA22

    Can someone simply solve this attachment.
    Thanks
    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,450
    Articles
    44
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hi there, and welcome to the forum.

    Just as a heads up, it's helpful when asking for help to actually give us some context as to what the question is, and what you've tried in order to solve it.

    I'm guessing that you're trying to figure out why the sum formula isn't returning a total. It's because each of your "numbers" has a space after it. If you get ride of those, it will work.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Sorry for the vagueness.
    Yes I know the spaces are the issue but when its a very long column to remove them is a pain so I was hoping there was an easier way.
    Thanks

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,450
    Articles
    44
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Okay, we've got a couple of methods to work with:

    1. Use a formula. The following assumes that everything in the column starts with $ and ends with a space:
    =VALUE(MID(A1,2,LEN(A1)-2))
    • To use it, enter it in B1 and copy down
    • Copy the entire column B
    • Right click, choose Paste Special-->Values
    2. Use find & replace. Whatever this is, it actually isn't a space. So edit one of the cells, and:
    • Place the cursor immediately after the last visible character
    • Hold down SHIFT and press your right arrow
    • Press CTRL+C to copy the character
    • Hit Enter to return to the grid
    • Press CTRL+H
    • In the "Find What" box, press CTRL+V to paste the character
    • Click the "Options" button to expand the options
    • Click Replace All
    The numbers should all automatically convert.

    Personally, I'd go with option 2. It looks like more work but will actually go faster.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Sensational!!!
    Thanks

  6. #6
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    401
    Articles
    0
    Excel Version
    Excel 2016
    You can often get non printing characters when importing data this page will help you understand what they are http://office.microsoft.com/en-ca/ex...003056131.aspx
    Kind regards,
    Simon Lloyd

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •