Excel List Problem

echo_7

New Member
Joined
Nov 25, 2005
Messages
2
I have a very large list in Excel that contains about 3500 companies. The list includes 8 columns (Company Name, Prefix, First Name, Last Name, Street , City, State, Zip). When I do a data merge with Word to create mailing labels it comes out somewhat messed up. It looks somethiing like this:

ABC Company
Mr.________John_______Doe
City Name,_______CA_________44065

The "__________" are not visiable on the labels but are used to represent the long spaces between the words.

After trying to figure out why I had all these extra spaces on my labels I went back to my Excel spreasheet and realized I have several blank spaces after each word in my columns. Using the example above I would have "City Name" followed by a bunch of black spaces as if I'd hit the space bar several times after entering "City Name". I found out that when I deleted the extra spaces and did the data merge for mail lables again the problem was resolved. Of course to go into something like 10,000 cell to remove the blank spaces would take forever so mu questions is this. Is there are way to somehow globally, or by column remove the blank spaces without having to go back to each cell and do it manually? If anyone could tell me how resolve this that would be great! :-D
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
If it is a consistance number of spaces you could go to _Edit , Find and replace and take care of the problem.
 
Upvote 0
Hi echo_7:

Welcome to MrExcel Board!

In line with dantb's suggestion, select all the cells, then invoke EDIT|Replace and

in the Find_what box ... key-in * (two spaces and asterik)

with nothing in Replace_with box and then

Click on Replace_All
__________________________________________________________________

If you have problems with the above approach another way is to use the VBA's RTRIM function ...
Code:
Sub yRangeRTrim()
    ' Ihave set my range to use RTrim as A2:C? ... select your range to suit
    Set yRange = Range("a2:C" & [a2].End(xlDown).Row)
    For Each cell In yRange
        cell.Value = RTrim(cell.Value)
    Next cell
End Sub
This should remove all the trailing spaces.
 
Upvote 0
Yogi Rocks!

Thanks so much for your help. Your solution worked perfectly and saved me a ton of time!. Thanks for making your answer to my question easy to understand for a non-Excel guru.

Cheers,

Echo_7
 
Upvote 0

Forum statistics

Threads
1,226,455
Messages
6,191,142
Members
453,643
Latest member
adamb83

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top