Find and delete trailing spaces?

[Rick Copeland]

What's the best way to find and delete trailing spaces in a column of data?
The column is "cities," and it's messing up my data merge because I'm trying
to print envelopes and I'm getting "city name , state" instead of "city
name, state".

I can think of no simple way to do it with search and replace. I have over
500 rows in the spreadsheet, and it's a bit onerous to do it by hand...


Thanks in advance,
Rick Copeland

 

[Bob Greenblatt]

What's the best way to find and delete trailing spaces in a column of data?
The column is "cities," and it's messing up my data merge because I'm trying
to print envelopes and I'm getting "city name , state" instead of "city name,
state".

I can think of no simple way to do it with search and replace. I have over
500 rows in the spreadsheet, and it's a bit onerous to do it by hand...


Thanks in advance,
Rick Copeland

Use the TRIM function in another column to strip the trailing blanks. Then
copy the results and paste-special-values back into the original column.

 

[Rick Copeland]

Use the TRIM function in another column to strip the trailing blanks. Then
copy the results and paste-special-values back into the original column.

Bob, that worked like a charm... Thanks!!!!

Rick

 

[Guest]

-----Original Message-----
What's the best way to find and delete trailing spaces in a column of data?
The column is "cities," and it's messing up my data merge because I'm trying
to print envelopes and I'm getting "city name , state" instead of "city
name, state".

I can think of no simple way to do it with search and replace. I have over
500 rows in the spreadsheet, and it's a bit onerous to do it by hand...


Thanks in advance,
Rick Copeland

In the particular case you show, you can replace " ,"
with ",". The space you want to remove is not trailing,
right? Otherwise, I suggest you look at the code where
someone wants everything to be upper case. I'm sure you
can modify the macro suggested such that it will remove
blanks correctly.

/Fredrik

 

[JE McGimpsey]

What's the best way to find and delete trailing spaces in a column of data?
The column is "cities," and it's messing up my data merge because I'm trying
to print envelopes and I'm getting "city name , state" instead of "city
name, state".

I can think of no simple way to do it with search and replace. I have over
500 rows in the spreadsheet, and it's a bit onerous to do it by hand...

take a look at David McRitchie's TrimALL() macro:

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