Home 
Search 
Today's Posts 
#1




Remove space in front of number in a cell?
I have a column of values I wish to sum. However, the data was imported from
a database and is entered as text  e.g $,100,000 I have removed the $ (=RIGHT) sign and the , (=SUBSTITUTE) but have now found that there is a space in front of the number. Is there a simple way to remove the space? I have used REPLACE but this does not work. I have used =RIGHT but this removes the next number not the space. Any suggestions? 
#2




Remove space in front of number in a cell?
Hi,
Try this =TRIM(SUBSTITUTE(A1,"$,",""))*1 Mike "Barcelona" wrote: I have a column of values I wish to sum. However, the data was imported from a database and is entered as text  e.g $,100,000 I have removed the $ (=RIGHT) sign and the , (=SUBSTITUTE) but have now found that there is a space in front of the number. Is there a simple way to remove the space? I have used REPLACE but this does not work. I have used =RIGHT but this removes the next number not the space. Any suggestions? 
#3




Remove space in front of number in a cell?
=VALUE(TRIM(SUBSTITUTE(RIGHT(A1,LEN(A1)1),",","")))
returns the pure number. Regards, Stefi „Barcelona” ezt *rta: I have a column of values I wish to sum. However, the data was imported from a database and is entered as text  e.g $,100,000 I have removed the $ (=RIGHT) sign and the , (=SUBSTITUTE) but have now found that there is a space in front of the number. Is there a simple way to remove the space? I have used REPLACE but this does not work. I have used =RIGHT but this removes the next number not the space. Any suggestions? 
#4




Remove space in front of number in a cell?
Hi,
I have not reied this but enter 1 in any cell. Then copy it and select the range. Now go to Edit paste Special Multiply.  Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "Barcelona" wrote in message ... I have a column of values I wish to sum. However, the data was imported from a database and is entered as text  e.g $,100,000 I have removed the $ (=RIGHT) sign and the , (=SUBSTITUTE) but have now found that there is a space in front of the number. Is there a simple way to remove the space? I have used REPLACE but this does not work. I have used =RIGHT but this removes the next number not the space. Any suggestions? 
#5




Remove space in front of number in a cell?
THANKS Stefi  don't understand the formula logic, but it worked!
"Stefi" wrote: =VALUE(TRIM(SUBSTITUTE(RIGHT(A1,LEN(A1)1),",",""))) returns the pure number. Regards, Stefi „Barcelona” ezt *rta: I have a column of values I wish to sum. However, the data was imported from a database and is entered as text  e.g $,100,000 I have removed the $ (=RIGHT) sign and the , (=SUBSTITUTE) but have now found that there is a space in front of the number. Is there a simple way to remove the space? I have used REPLACE but this does not work. I have used =RIGHT but this removes the next number not the space. Any suggestions? 
#6




Remove space in front of number in a cell?
Mike  Thanks for suggestion. Unfortunately returns a VALUE error.
Stefi's suggestion does though work, so OK for now. "Mike H" wrote: Hi, Try this =TRIM(SUBSTITUTE(A1,"$,",""))*1 Mike "Barcelona" wrote: I have a column of values I wish to sum. However, the data was imported from a database and is entered as text  e.g $,100,000 I have removed the $ (=RIGHT) sign and the , (=SUBSTITUTE) but have now found that there is a space in front of the number. Is there a simple way to remove the space? I have used REPLACE but this does not work. I have used =RIGHT but this removes the next number not the space. Any suggestions? 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Macro to remove space at front and end of a cell  Excel Discussion (Misc queries)  
Insert a letter in front of number in every cell?  Excel Worksheet Functions  
How do I remove a space infront of text in a cell?  Excel Discussion (Misc queries)  
how to add a letter in front of each number in a cell  Excel Discussion (Misc queries)  
Function to remove a space from text in cell WITHOUT macro??  Excel Worksheet Functions 