delete ALL spaces in text string

[choeg]

Hi,

I've been trying to remove all spaces from a text string, but can't get it to work. The TRIM() function only removes some spaces, but not all...

Problem:
I paste info from another application into Excel in the following format.
e.g. "100 000.00" (could also be 100 000 000.10, or many other combos).
This is interpreted as text in Excel, since there are spaces hard coded in between the numbers.

Goal:
Have Excel interpret this as a number. (e.g. "100000").

For some reason I can't get the (text) substitute function to work (e.g. substitute(A1," ", "").

I am using Excel 97. I don't mind using VBA, but would prefer not to.

Any help would be appreciated.

/Carl

 

[Andrew Poulsom]

The formula:

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

worked for me.

 

[choeg]

The formula:

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

worked for me.

At first I was going to write "fine for you, but it doesn't work for me", but after experimenting a bit I found out that it works for me if I type in
"100 000.00" and runs the formula for that cell. However, it doesn't work for the data I have pasted (from the application)!

Is there another character that looks like a space? How can I figure out which one it is (and remove it)?

/Carl

 

[goblin]

It could be that the cells containing the data is formatted to display spaces but doesn't actually have any...

If you pasted the data from another application, that isn't unlikely.

 

[Richie(UK)]

This thread is now being continued here:

http://www.mrexcel.com/board2/viewtopic.php?t=55676