Remove trailing spaces from multiple columns in Excel

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.

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



"dcaissie" wrote:

> 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.

Hi!

See this:

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

Biff

"dcaissie" <[email protected]> wrote in message
news:[email protected]...
>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.

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.

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

"Tim Whitley" wrote:

> 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
>
>
>
> "dcaissie" wrote:
>
> > 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.

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" wrote:

> 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.

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


"dcaissie" wrote:

> 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" wrote:
>
> > 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.