Strip leading spaces from cell
 

I need a formula to remove/delete leading spaces from a cell.

column b contains data imported from anothe application. unfortuantly some
of these cells have padded leading blanks added.

I need to remove these. but 3000+ cells individually will take hours.

any help?

Hi Pete,

Try this:

=TRIM(A1)

Regards,
KL


"Pete" wrote in message
...
I need a formula to remove/delete leading spaces from a cell.

column b contains data imported from anothe application. unfortuantly some
of these cells have padded leading blanks added.

I need to remove these. but 3000+ cells individually will take hours.

any help?

If they are from another application they may not be spaces,
they could be CHAR(160) which is a Required blank (RBL),
or in HTML is know as a nonbreaking space ( )

If you really only want to trim the leading space you would use
LTRIM in VBA but you probably want to trim both sides as previously
suggested.

I would recommend doing this with a macro so that you can change
things in place rather than the little dance routine of creating a helper
column, converting that column to constants and then removing the
original. If you enjoy doing the extra steps and it is CHAR(160)
you could look at my strings.htm page and use something like
=TRIM( SUBSTITUTE(A21, CHAR(160)," ") )
there is no LTRIM function in worksheet functions but there is in VBA

But for a macro to trim both sides and possible excessive spaces in between see
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"KL" wrote in message ...
Hi Pete,

Try this:

=TRIM(A1)

Regards,
KL


"Pete" wrote in message
...
I need a formula to remove/delete leading spaces from a cell.

column b contains data imported from anothe application. unfortuantly some
of these cells have padded leading blanks added.

I need to remove these. but 3000+ cells individually will take hours.

any help?

ASAP Utilities, a free add-in at www.asap-utilities.com includes this
capability among it's many nifty features.........

Vaya con Dios,
Chuck, CABGx3


"Pete" wrote in message
...
I need a formula to remove/delete leading spaces from a cell.

column b contains data imported from anothe application. unfortuantly some
of these cells have padded leading blanks added.

I need to remove these. but 3000+ cells individually will take hours.

any help?