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

D

Debi

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.."
 
R

Ron Coderre

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
 
H

Harlan Grove

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.."

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.
 
D

Debi

Thank you I used the option from the Data Menu and it works perfectly
providing the exact results I want.
 
D

Debi

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?
 
D

Debi

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.
 
D

Dave Peterson

Data|text to columns will remove trailing spaces, too.
(Doesn't sound like a problem for the OP, though.)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top