Formula to convert a number with spaces to just a number?

  • Thread starter Thread starter WolfgangPD
  • Start date Start date
W

WolfgangPD

We import GL data in a CSV file and it gives year to data balances. The
problem is the number has spaces where a comma should be. For instance,
1,700,000.02 is in as 1 700 000.02 If the number is in the thousands there
are two spaces in front of the first digit, in the millions one digit. If
the number is less than a thousand than it reads as a number. Is there a
formula to remove the space(s) so Excel recognizes it as a number?
 
Try this in B1:

=--SUBSTITUTE(A1," ","")

assuming your "number" is in A1.

Hope this helps.

Pete
 
If you have your 'numbers' in Col A then you can enter this in any Col
starting at row 1 (if you don't have header rows)

=SUBSTITUTE(K3," ","")*1

Copy this down

First you use Substitute to replace spaces (" ") with nothing ("") then
multiply with 1 to convert to number
 
Thank you very much! Adding the zero turned it into a number instead of
text. This is perfect!
 
We import GL data in a CSV file and it gives year to data balances. The
problem is the number has spaces where a comma should be. For instance,
1,700,000.02 is in as 1 700 000.02 If the number is in the thousands there
are two spaces in front of the first digit, in the millions one digit. If
the number is less than a thousand than it reads as a number. Is there a
formula to remove the space(s) so Excel recognizes it as a number?
Click to expand...

=substitute(a1," ","")

--ron
 
You're welcome.

The -- converts the text value to a number, but you can use +0 or *1
instead.

Pete
 
Didn't you notice that Pete's formula accomplishes exactly the same result
by using the unary!
 
Select the range of cells in question
Goto the menu Edit>Replace
Find what: enter a space by pressing the space bar
Replace with: nothing, leave this blank
Replace All
 
Back
Top