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
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