Numbers that are not treated as numbers

**rolferik** · 05-08-2007, 03:34 AM

I have a problem with a report which is generated using another business reporting tool.
In the column that displays amounts, not all the numbers are treated as such. There is a space acting as a 1000 separator, and in order to have these amonts act like numbers I need to remove this space. If I try to use the Replace function and substitute space for nothing, there is no change.
Does anybody know what causes some of the numbers to be displayed correctly as numbers and other incorrectly, and hopefully how to fix this?
I'd be grateful for any and all tips!

RE

**sweep** · 05-08-2007, 03:41 AM

Hi.

Try this;

=VALUE(SUBSTITUTE(A1," ",""))

to remove the space and convert to a number.

Excel is treating the imported information as text, probably because of the space. The VALUE function converts this "numerical text" back into a number.

**arthurbr** · 05-08-2007, 03:42 AM

Welcome to the board
Using XL2003 replacing a space with nothing worked perfectly.
Could you past a sample of your data?

Numbers that are not treated as numbers

LinkBack

Thread Tools

Rate This Thread

Display

Numbers that are not treated as numbers

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions