Remove trailing spaces from multiple columns in Excel

[dcaissie]

I have an Excel spreadsheet of names, addresses, etc. that I need to use in a
mail merge to create labels. Several of the columns contain data with
trailing spaces. I know how to use the TRIM command to remove trailing
spaces one cell at a time, but is there a way to remove trailing spaces from
multiple cells at once? There are 4 contiguous columns of 133 rows
containing text with trailing spaces, and I don't relish removing those
spaces one cell at a time.

 

[Tim Whitley]

You can use the "replace" command.

Select the fields that you want to perform this operation on
Edit (on the toolbar)
Select "Replace"
put a space in the find field
put nothing in the replace field

Let me know if this is unclear

 

[Biff]

Hi!

See this:

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

Biff

 

[Joseph in Atlanta]

Hi Dcaissie,

From your comments, it sounds like you want to remove trailing spaces, but
preserve internal space characters... so You don't want to do a search and
replace.
Your message said:
I know how to use the TRIM command to remove trailing spaces
one cell at a time, but is there a way to remove trailing spaces from
multiple cells at once? (4 contiguous columns of 133 rows)

Here's another solution:

Lets say you have data in column B1:B133
create another blank column "C" and insert =TRIM(B1) into C1
C1 now contains the trimmed version of B1
Copy/Paste this formula in 133 rows of C1:C133 (highlite all cells and
Ctrl-V)
Now, with all 133 cells of row C hilighted, Press Ctrl-C again to copy TRIM
data
Go back to Cell B1, and PASTE-Special "VALUES" only
You can do this with Alt-E --> S --> V

Now ALL of your data is trimmed, and you have pasted the trimmed DATA into
column B, not just the Functions from column C.

I think that will help you.
Post a reply to let us know.

 

[dcaissie]

Hi Tim,

Thanks for your response; I appreciate the help. Your suggestion worked a
little bit too well. It removed the spaces, including the spaces between
words. Oh well.

Thanks for your help!

=>Donna

 

[dcaissie]

Hi Joseph,

Thanks for your suggestion; it worked beautifully! You saved much time and
angst. Thank you! Thank you! Thank you!

=>Donna

 

[Joseph in Atlanta]

I'm glad that this helped you.
Thanks for the feedback.

From this example, I think you will find repeated uses for the following
techniques:

1) You can let a formula do the work, instead of editing by hand
2) You can cut/paste ONE formula into 1000 rows, and Excell modifies the
cell reference (E6:G8 style) to match relative locations in all pasted
cells.
3) To modify/edit text data, it's often usefull to make another column for
temp work
4) Once you have data as you want it, using cut then
Edit->Paste_Special->Values
can let you set the Good data back in place of the "rough data"

Side notes:
a) If you don't want cell referrences modified, use '$' (look up Absolute
address)
b) Using Data->Import_Date can let you load info into spreadsheets more easily

 

[freetry]

I have a two spreadsheets with the same information and want to compare
account ID, which is on both sheets and on a match, change the information in
one field.

 

[Reitanos]

You want to do a match, but there are extra spaces in one set of data?
If that's what you're asking, you can use the TRIM function to remove
extra spaces before trying the match. If not, you may have to be more
precise is asking your question.