Strange Number as Text conversion problem...

[abm007]

I got a table of numbers from an email, but for some reason I'm unable to convert them to numbers and use them in formulas without the dreaded #VALUE! error. The problem seems to be associated with leading spaces, but I've tried all the usual ways to get around that including:

• Adding +0
• Multiplying * 1
• Number format
• VALUE()
• NUMBERVALUE(,".",",")
• TRIM()
• CLEAN()
• NUMBERVALUE(TRIM(CLEAN()),".",",")

The only thing that seems to work is manually deleting the leading spaces or re-typing the numbers. For this small data set, I just went ahead and manually deleted the leading spaces, but it's really bugging me that I can't figure out what is causing this strange behavior and the next data set may be much bigger. I've tried searching the forum and Google, but most of those just link to a more basic version of this problem (like, "Use Value()").

Small file is on dropbox.

Thanks in advance for helping clear up the mystery.

 

[Weazel]

maybe something like...

=SUBSTITUTE(A5,CHAR(160),"")+0

 

[Jonmo1]

The problem is they're NOT spaces.
They're 'Hard Returns', ASCI code 160

Try highlighting the numbers, and use Find/Replace (Press CTRL+F)

In the FindWhat box, hold down the ALT key and type 0160 with the numberpad on the right side of your keyboard
Leave the ReplaceWith box empty
Replace All.

 

[Joe4]

Welcome to the Board!

Data that comes from outside sources (like email, the web, etc) often has "special characters", like "non-breaking spaces" which are not handled by the same tools used to handle regular spaces (i.e. "TRIM" will not work on it).

The first thing to do is to identify exactly what you are doing with. Let's say that you have an entry in cell A1 that has one of these "mystery" characters at the beginning. Use this formula to find out what it is:

=CODE(LEFT(A1,1))

That formula will return the ASCII code of the first character. We can then look up what it is (Ascii Table - ASCII character codes and html, octal, hex and decimal chart conversion).


Edit: I am a slow typist and see you already got two replies showing you how to fix it. However, my reply above tells you how you can identify what you are dealing with. Once you know that, you can use that code in the solutions, like Weazel and jonmo1 did).

So, now you know how to identify and fix those issues yourself going forward.

 

[abm007]

Thanks everyone! That explains it. Three replies in record time, quite a helpful group you are!

Thanks!

 

[Jonmo1]

You're welcome.