how do I remove leading spaces and leave the remianing spaces w

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am attempting to remove any leading spaces should there be any within a
range of cells
Example:

In Cell B16 " find the spaces"
in Cell B17 " Must leave other spaces"
In Cell B18 "May have no leading spaces"

The only spaces I would want to eliminate are the ones before "find...." and
"Must.."
 
Try the TRIM function:

With
B16: " find the spaces"

C16: =TRIM(B16)
Returns "find the spaces"....without the quotes, of course.

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
Debi said:
I am attempting to remove any leading spaces should there be any
within a range of cells
Example:

In Cell B16 " find the spaces"
in Cell B17 " Must leave other spaces"
In Cell B18 "May have no leading spaces"

The only spaces I would want to eliminate are the ones before
"find...." and "Must.."
Click to expand...

With formulas,

=REPLACE(B16,1,FIND(LEFT(TRIM(B16),2),B16)-1,"")

This will remove only the leading spaces, not trailing spaces or
sequences of multiple spaces within the text.

With menu commands, select B16:B18, run Data > Text to Columns, select
Fixed Width, click the Next button, remove *ALL* the break lines by
double clicking on them, then click the Finish button. This is a
useful Excel quirk when parsing single fields.
 
Thank you I used the option from the Data Menu and it works perfectly
providing the exact results I want.
 
This worked however it return the data to the cell the formua is in. It would
work if there was a small amount of data to correct however I have a large
listing to remove intermittal leading blanks.
My desire results is to have the range of data corrected remaining in their
original cells.
Is this possible?
 
While the option from the data menu worked could you please explain How the
Replace function works as well as what each peice of the formula represents.
 
Data|text to columns will remove trailing spaces, too.
(Doesn't sound like a problem for the OP, though.)
 
Back
Top