Results 1 to 10 of 10
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    What's in column B

    What's in column B?

    I've tried multiplying by 1 (no luck).
    I've tried CLEAN and TRIM and both of them together (no luck).

    What is going on with column B that I can't get them to be REAL numbers?

    I ended up doing: =CODE(left(b1,1)) and found it was character 160 (whatever that is).
    Then, substituted CHAR(160) with a "" .

    Is that the only way??
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    A quick google reveals this to be a non-breaking space, which as you have seen is not removed by TRIM or CLEAN.
    A suggested workaround is to use a formula such as =SUBSTITUTE(B2,CHAR(160),"") in another column and calculate with that.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You can also do a find replace on char 160 (in the find box enter 0160 with the numeric keypad) and replace with nothing.That makes the cells, number-text, which can be converted by selecting a blank cell in the workbook, copying it to the clipboard, then selecting the cells to convert and paste-special - ADD to convert them to numbers

    Steve

  4. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Curiously, when opened in Excel 2003 column B behaves exactly as a number - it seems that the conversion from .xlsx to xls strips out the non-breaking spaces !

    Maybe that suggests a possible (unusual) way to "clean up" those annoying characters if you have a lot of them ?

  5. #5
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Joliette, Quebec, Canada
    Posts
    290
    Thanks
    9
    Thanked 0 Times in 0 Posts
    I have had this problem I used search and replace function putting alt 0160 in the search, and nothing in the replace. you could make a macro to strip the non-breaking space.

  6. #6
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Er, if the column contains useless info - why not delete it?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  7. #7
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Quote Originally Posted by WebGenii View Post
    Er, if the column contains useless info - why not delete it?
    isn't the point that the OP wants to do "normal" calculations with the numbers in column B, but they're not really "numbers" as they stand because of the additional character?

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    EXACTLY. And, as I stated in my original post, I already knew to do the replacement of the CHAR(160)...but was wondering if there was another way. Initially, I stated:

    I ended up doing: =CODE(left(b1,1)) and found it was character 160 (whatever that is -- not I know it's a non-breaking space).
    Then, substituted CHAR(160) with a "" .

    Is that the only way??

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I posted an alternative way ...Steve

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I saw yours, Steve...thanks. Problem solved.

Posting Permissions

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