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

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?

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

Try this in B1:

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

assuming your "number" is in A1.

Hope this helps.

Pete


On Dec 16, 12:16*am, WolfgangPD
wrote:
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?

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

Try in B1, copied down: =SUBSTITUTE(A1," ","")+0
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---
"WolfgangPD" wrote:
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?

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

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

"WolfgangPD" wrote:

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?

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

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

"Max" wrote:

Try in B1, copied down: =SUBSTITUTE(A1," ","")+0
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---
"WolfgangPD" wrote:
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?

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

Thanks Pete.

"Pete_UK" wrote:

Try this in B1:

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

assuming your "number" is in A1.

Hope this helps.

Pete


On Dec 16, 12:16 am, WolfgangPD
wrote:
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?

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

On Mon, 15 Dec 2008 16:16:00 -0800, WolfgangPD
wrote:

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?

=substitute(a1," ","")

--ron

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

You're welcome.

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

Pete

On Dec 16, 12:35*am, WolfgangPD
wrote:
Thanks Pete. *



"Pete_UK" wrote:
Try this in B1:

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

assuming your "number" is in A1.

Hope this helps.

Pete

On Dec 16, 12:16 am, WolfgangPD
wrote:
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?- Hide quoted text -

- Show quoted text -

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

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"WolfgangPD" wrote in message
...
Thank you very much! Adding the zero turned it into a number instead of
text. This is perfect!

"Max" wrote:

Try in B1, copied down: =SUBSTITUTE(A1," ","")+0
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---
"WolfgangPD" wrote:
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?

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

Glad it worked. Pl press the YES button in my response, won't you. Think you
missed doing that.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---
"WolfgangPD" wrote:
Thank you very much! Adding the zero turned it into a number instead of
text. This is perfect!

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

Select the range of cells in question
Goto the menu EditReplace
Find what: enter a space by pressing the space bar
Replace with: nothing, leave this blank
Replace All

--
Biff
Microsoft Excel MVP


"WolfgangPD" wrote in message
...
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?