Strange Number as Text conversion problem...

abm007

New Member
Joined
Mar 23, 2016
Messages
2
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.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
maybe something like...

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

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,492
Office Version
  1. 365
Platform
  1. Windows
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:
=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.
 
Last edited:

abm007

New Member
Joined
Mar 23, 2016
Messages
2
Thanks everyone! That explains it. Three replies in record time, quite a helpful group you are!

Thanks!
 

Forum statistics

Threads
1,176,004
Messages
5,900,816
Members
434,855
Latest member
backspace18

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top