Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I convert the result of a formula to a whole number? | Excel Discussion (Misc queries) | |||
how do I convert a number to number of years, months and days | Excel Worksheet Functions | |||
How do I convert time (2:30) to a number (2.5) in a formula? | Excel Worksheet Functions | |||
convert text-format number to number in excel 2000%3f | Excel Discussion (Misc queries) | |||
not able to convert text, or graphic number to regular number in e | Excel Worksheet Functions |