Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,646
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert a letter in front of number in every cell? [email protected] Excel Worksheet Functions 4 April 2nd 23 08:06 PM
Macro to remove space at front and end of a cell yhoy Excel Discussion (Misc queries) 2 February 22nd 08 10:53 PM
How do I remove a space infront of text in a cell? GAC Excel Discussion (Misc queries) 7 October 10th 06 01:41 AM
how to add a letter in front of each number in a cell nmodafferi Excel Discussion (Misc queries) 15 June 16th 05 08:58 PM
Function to remove a space from text in cell WITHOUT macro?? [email protected] Excel Worksheet Functions 5 May 28th 05 02:28 AM


All times are GMT +1. The time now is 12:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"