Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: SumA22

  1. #1

    SumA22



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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,048
    Articles
    79
    Blog Entries
    14
    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 (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    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,048
    Articles
    79
    Blog Entries
    14
    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 (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    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
    323
    Articles
    0
    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
    Microsoft Office Discussion

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,048
    Articles
    79
    Blog Entries
    14
    Good link, Simon. This is actually the first time I've seen a non-printing character that rendered as a space, rather than a square icon. I actually attacked it with TRIM, which didn't do any good. Interestingly enough, CLEAN doesn't get it either. I woudl have expected that to work.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    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.

  8. #8
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    323
    Articles
    0
    Im not entirely sure what it is, it could be a Unicode control character, what would help is if Lyfsabuz told us where or from what program...etc the data was copied from then we could narrow it down and deal with it properly

    EDIT

    Straight from the help files

    Description
    Removes all nonprintable characters from text. Use CLEAN on text imported from other applications that contains characters that may not print with your operating system. For example, you can use CLEAN to remove some low-level computer code that is frequently at the beginning and end of data files and cannot be printed.
    Important The CLEAN function was designed to remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from text. In the Unicode character set (Unicode: A character encoding standard developed by the Unicode Consortium. By using more than one byte to represent each character, Unicode enables almost all of the written languages in the world to be represented by using a single character set.), there are additional nonprinting characters (values 127, 129, 141, 143, 144, and 157). By itself, the CLEAN function does not remove these additional nonprinting characters. For an example of how to remove these additional nonprinting characters from text, see Remove spaces and nonprinting characters from text.
    Remove spaces and nonprinting characters from text
    Show AllHide All
    Sometimes text values contain leading, trailing, or multiple embedded space characters (Unicode character set (Unicode: A character encoding standard developed by the Unicode Consortium. By using more than one byte to represent each character, Unicode enables almost all of the written languages in the world to be represented by using a single character set.) values 32 and 160), or non-printing characters (Unicode character set values 0 to 31, 127, 129, 141, 143, 144, and 157). These characters can sometimes cause unexpected results when you sort, filter, or search. For example, users may make typographical errors by inadvertently adding extra space characters, or imported text data from external sources may contain nonprinting characters embedded in the text. Because these characters are not easily noticed, the unexpected results may be difficult to understand. To remove these unwanted characters, you can use a combination of the TRIM, CLEAN, and SUBSTITUTE functions.
    The TRIM function removes spaces from text except for single spaces between words. The CLEAN function removes all nonprintable characters from text. Both functions were designed to work with 7-bit ASCII, which is a subset of the ANSI character set (ANSI character set: An 8-bit character set used by Microsoft Windows that allows you to represent up to 256 characters (0 through 255) by using your keyboard. The ASCII character set is a subset of the ANSI set.). It's important to understand that the first 128 values (0 to 127) in 7-bit ASCII represent the same characters as the first 128 values in the Unicode character set.
    The TRIM function was designed to trim the 7-bit ASCII space character (value 32) from text. In the Unicode character set, there is an additional space character called the nonbreaking space character that has a decimal value of 160. This character is commonly used in Web pages as the HTML entity,  . By itself, the TRIM function does not remove this nonbreaking space character.
    The CLEAN function was designed to remove the first 32 non-printing characters in the 7 bit ASCII code (values 0 through 31) from text. In the Unicode character set, there are additional nonprinting characters (values 127, 129, 141, 143, 144, and 157). By itself, the CLEAN function does not remove these additional nonprinting characters.
    To do this task, use the SUBSTITUTE function to replace the higher value Unicode characters with the 7-bit ASCII characters for which the TRIM and CLEAN functions were designed.
    Last edited by Simon Lloyd; 2011-04-14 at 08:49 PM. Reason: Added excel reference
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  9. #9
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,048
    Articles
    79
    Blog Entries
    14
    You know... I think Find & Replace wins for ease of use in this case.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    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.

  10. #10
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    323
    Articles
    0
    Quote Originally Posted by Ken Puls View Post
    You know... I think Find & Replace wins for ease of use in this case.
    Of course it does but aren't you curious and want to rise to the challenge of a worksheet function or to eliminate it on import?
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

Page 1 of 2 1 2 LastLast

Posting Permissions

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