Remove leading space

[Guest]

I wonder if someone can help me with this...it's driving me crazy.

I need to delete a leading space from multiple cells, 200 or more. The
leading space is in front of text that is often more than one word so I can't
just copy into Word and do a find and replace on the spaces.

I looked in many places for help and found out about the Trim function but
when I tried it, it didn't work for me. Am I doing it wrong? I created a
column next to the column that I want to remove the leading spaces from. then
I put =TRIM(B2) in the first cell, =TRIM (B3) in the next one, etc. But all
it did was put the exact same thing as before (with the leading space still
in it) in that column?

Any suggestions? Thanks!

- Kari

 

[Guest]

The trim function should have worked if it was just a space entered w/the
spacebar.

It could be a special character that appears as a space. Try
Search/Replace. In the search line, hold down the Alt key and hit 255 on the
number keypad. Then hit Replace All.

Be sure to back up your workbook.

 

[Biff]

Hi!

Maybe those leading spaces aren't your standard leading spaces, char 32!
They may be some other unseen character like the dreaded char 160.

Try finding out what the actual characters are. Use this formula in a
temporary helper column:

=CODE(B2)

Once you know what those characters are then they should be fairly easy to
get rid of using either Edit>Replace or, maybe a formula like:

=MID(B2,2,255)

Or:

=SUBSTITUTE(B2,LEFT(B2),"")

Then you could convert the formulas to constants by doing a Copy>Paste
Special>Values. Then deleting the original data.

Biff

 

[Paul Skinner]

I wonder if someone can help me with this...it's driving me crazy.

I need to delete a leading space from multiple cells, 200 or more. The
leading space is in front of text that is often more than one word so I can't
just copy into Word and do a find and replace on the spaces.

I looked in many places for help and found out about the Trim function but
when I tried it, it didn't work for me. Am I doing it wrong? I created a
column next to the column that I want to remove the leading spaces from. then
I put =TRIM(B2) in the first cell, =TRIM (B3) in the next one, etc. But all
it did was put the exact same thing as before (with the leading space still
in it) in that column?

Any suggestions? Thanks!

- Kari

Try :- Insert an empty column on the right of your Data, select the column
you want to fix. Go to top menu- Data, Text to column, This will show you a
preview of the text You wish to fix. Click next, Click the checkbox space,
Click next, click finish. Done. Then You can copy data back to where you
want it.
Paul

 

[Dave Peterson]

If you're getting this data from a web page, you may have non-breaking spaces
(HTML stuff) in those cells.

You may want to try David McRitchie's routine to clean that stuff up:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

=========
If you want to try a formula, this may work:

=trim(substitute(a1,char(160)," "))

It changes the non-Breaking character to a space, then trims the value.

Excel also has =clean() that you may want to review, too.