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

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

Pete_UK

Try this in B1:

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

assuming your "number" is in A1.

Hope this helps.

Pete
 
S

Sheeloo

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
 
W

WolfgangPD

Thank you very much! Adding the zero turned it into a number instead of
text. This is perfect!
 
R

Ron Rosenfeld

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
 
P

Pete_UK

You're welcome.

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

Pete
 
R

RagDyer

Didn't you notice that Pete's formula accomplishes exactly the same result
by using the unary!
 
T

T. Valko

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Creating a Chart on form from selected data in query. 0
How can I keep a zero at the beginning of a mobile number? 4
convert number to % using only custom number format challenge 7
XL2007 French Numeric Formats 1
Number range as a percentage 2
format to a specific number of spaces 2
How add a digit to front of number in each cell in excexl column 1
Complicated Formula Problem 10

Top