software

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 quot;replacequot; command.

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

Let me know if this is unclear
quot;dcaissiequot; wrote:

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

Hi!

See this:

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

Biff

quot;dcaissiequot; gt; wrote in message
...
gt;I have an Excel spreadsheet of names, addresses, etc. that I need to use in
gt;a
gt; mail merge to create labels. Several of the columns contain data with
gt; trailing spaces. I know how to use the TRIM command to remove trailing
gt; spaces one cell at a time, but is there a way to remove trailing spaces
gt; from
gt; multiple cells at once? There are 4 contiguous columns of 133 rows
gt; containing text with trailing spaces, and I don't relish removing those
gt; 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 quot;Cquot; 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 quot;VALUESquot; only
You can do this with Alt-E --gt; S --gt; 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!

=gt;Donna

quot;Tim Whitleyquot; wrote:

gt; You can use the quot;replacequot; command.
gt;
gt; Select the fields that you want to perform this operation on
gt; Edit (on the toolbar)
gt; Select quot;Replacequot;
gt; put a space in the find field
gt; put nothing in the replace field
gt;
gt; Let me know if this is unclear
gt;
gt;
gt;
gt; quot;dcaissiequot; wrote:
gt;
gt; gt; I have an Excel spreadsheet of names, addresses, etc. that I need to use in a
gt; gt; mail merge to create labels. Several of the columns contain data with
gt; gt; trailing spaces. I know how to use the TRIM command to remove trailing
gt; gt; spaces one cell at a time, but is there a way to remove trailing spaces from
gt; gt; multiple cells at once? There are 4 contiguous columns of 133 rows
gt; gt; containing text with trailing spaces, and I don't relish removing those
gt; gt; 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!

=gt;Donna

quot;Joseph in Atlantaquot; wrote:

gt; Hi Dcaissie,
gt;
gt; From your comments, it sounds like you want to remove trailing spaces, but
gt; preserve internal space characters... so You don't want to do a search and
gt; replace.
gt; Your message said:
gt; I know how to use the TRIM command to remove trailing spaces
gt; one cell at a time, but is there a way to remove trailing spaces from
gt; multiple cells at once? (4 contiguous columns of 133 rows)
gt;
gt; Here's another solution:
gt;
gt; Lets say you have data in column B1:B133
gt; create another blank column quot;Cquot; and insert =TRIM(B1) into C1
gt; C1 now contains the trimmed version of B1
gt; Copy/Paste this formula in 133 rows of C1:C133 (highlite all cells and
gt; Ctrl-V)
gt; Now, with all 133 cells of row C hilighted, Press Ctrl-C again to copy TRIM
gt; data
gt; Go back to Cell B1, and PASTE-Special quot;VALUESquot; only
gt; You can do this with Alt-E --gt; S --gt; V
gt;
gt; Now ALL of your data is trimmed, and you have pasted the trimmed DATA into
gt; column B, not just the Functions from column C.
gt;
gt; I think that will help you.
gt; 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-gt;Paste_Special-gt;Values
can let you set the Good data back in place of the quot;rough dataquot;

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

gt; Hi Joseph,
gt;
gt; Thanks for your suggestion; it worked beautifully! You saved much time and
gt; angst. Thank you! Thank you! Thank you!
gt;
gt; =gt;Donna
gt;
gt; quot;Joseph in Atlantaquot; wrote:
gt;
gt; gt; Hi Dcaissie,
gt; gt;
gt; gt; From your comments, it sounds like you want to remove trailing spaces, but
gt; gt; preserve internal space characters... so You don't want to do a search and
gt; gt; replace.
gt; gt; Your message said:
gt; gt; I know how to use the TRIM command to remove trailing spaces
gt; gt; one cell at a time, but is there a way to remove trailing spaces from
gt; gt; multiple cells at once? (4 contiguous columns of 133 rows)
gt; gt;
gt; gt; Here's another solution:
gt; gt;
gt; gt; Lets say you have data in column B1:B133
gt; gt; create another blank column quot;Cquot; and insert =TRIM(B1) into C1
gt; gt; C1 now contains the trimmed version of B1
gt; gt; Copy/Paste this formula in 133 rows of C1:C133 (highlite all cells and
gt; gt; Ctrl-V)
gt; gt; Now, with all 133 cells of row C hilighted, Press Ctrl-C again to copy TRIM
gt; gt; data
gt; gt; Go back to Cell B1, and PASTE-Special quot;VALUESquot; only
gt; gt; You can do this with Alt-E --gt; S --gt; V
gt; gt;
gt; gt; Now ALL of your data is trimmed, and you have pasted the trimmed DATA into
gt; gt; column B, not just the Functions from column C.
gt; gt;
gt; gt; I think that will help you.
gt; gt; Post a reply to let us know.


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.

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.

On May 16, 2:29 pm, freetry gt; wrote:
gt; I have a two spreadsheets with the same information and want to compare
gt; account ID, which is on both sheets and on a match, change the information in
gt; one field.