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

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

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

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" wrote:

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

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

Debi wrote...
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.

how do I remove leading spaces and leave the remianing spaces
 

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

"Harlan Grove" wrote:

Debi wrote...
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.

how do I remove leading spaces and leave the remianing spaces
 

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?

"Ron Coderre" wrote:

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" wrote:

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

how do I remove leading spaces and leave the remianing spaces
 

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.

"Harlan Grove" wrote:

Debi wrote...
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.

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

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

Harlan Grove wrote:

Debi wrote...
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.

--

Dave Peterson