formatting numbers
 

I'm using an internal system in my company where I get the results in excel
using som sort of american excel standard. the numbers come out with a space
(1000 separator) between numbers (one million is spelled 1 000 000).

This is not the 1000 separator you can choose in FORMAT-CELL-NUMBER but some
other standard that I can't convert to normal numbers.

The format doesn't recognize 1 000 like the number 1000. If i try to sum 1
000 + 1 000 (i.e 2000) i get the result 0 (zero).

My problem is that I can't work in this format in my excel 2000 (english). I
need to be able to sort the numbers, use them in formulas etc.

How do i format the "unreadable" numbers into real numbers i can use.

In a cell:

SUBSTITUTE(cell_address, " ","")

HTH

Art


--
HiArt
------------------------------------------------------------------------
HiArt's Profile: http://www.excelforum.com/member.php...o&userid=19953
View this thread: http://www.excelforum.com/showthread...hreadid=345810

Hi Art,
Thanks for helping, but I don't understand - I'm not too good with Functions
in Excel.

Could you clarify how it should look if I have the number
1 234 in cell D4 and want to have the number in a usable format 1234?

Where (in what cell) should I be when using SUBSTITUTE?

Also; how do I do to make the substitution apply for all cells in a column?

What does HTH mean? Is it part of what to do or just "Hope this helps"?

// Jonas

"HiArt" wrote:


In a cell:
SUBSTITUTE(cell_address, " ","")

HTH
Art

jonas

Select the column of data.

EditReplace

What: space

With: enter nothing here

Replace all.


Gord Dibben Excel MVP

On Wed, 16 Feb 2005 01:41:01 -0800, "jonas"
wrote:

I'm using an internal system in my company where I get the results in excel
using som sort of american excel standard. the numbers come out with a space
(1000 separator) between numbers (one million is spelled 1 000 000).

This is not the 1000 separator you can choose in FORMAT-CELL-NUMBER but some
other standard that I can't convert to normal numbers.

The format doesn't recognize 1 000 like the number 1000. If i try to sum 1
000 + 1 000 (i.e 2000) i get the result 0 (zero).

My problem is that I can't work in this format in my excel 2000 (english). I
need to be able to sort the numbers, use them in formulas etc.

How do i format the "unreadable" numbers into real numbers i can use.