Strip out extra spaces in a Cell

[Mikee21]

Hello,

I need to know how to strip out an extra space at the front or end of
cell. I am trying to sort this data and it won't sort properly wit
the extra character.

I tried the find and replace feature, but it didn't work for me. Also
I don't think it would work anyway because these cells have 2-
individual words and vary in length from 2-15 characters each.

Thanks,
Mike

PS - Be gentle I'm a first time user

 

[Ken Wright]

You probably had 'Match Exact Cell Contents' checked - Need to uncheck that, but
give that you have more than one word you would lose all spaces between them.

With your data in say Col A starting A1, in B1 put =TRIM(A1) and copy down.
When done, copy Col B and paste special over Col as values, then delete Col B.

 

[Norman Harker]

Hi Mikee21!

Try:

=TRIM(A1)

Removes spaces at beginning and end plus removes places exceed 1 space
between words.

Having used it.

Copy
Edit > Paste Special > Values > OK

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Mikee21]

Hello Ken,

Thanks for your help. I tried your suggestion, but I still have th
same problem. Any other suggestions?

BR, Mik

 

[Ken Wright]

OK, I mean this in the nicest possible way, but I think your data has garbage in
it By that I mean possibly left over html garbage. Have you used macros
before, in which case you should give Dave McRitchies Trimall macro a whirl.
You simply select the data concerned and run the macro and it cleans house for
you.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall