Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / June 2006

Tip: Looking for answers? Try searching our database.

Delete hidden spaces in cells

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Mark Christensen - 30 Jun 2006 14:41 GMT
Hello,

I've got Excel 2002 SP3.  I've got a big spreadsheet of names and addresses
that I use as a data source for a mail merge in Word.  If I select a cell in
the city column in Excel and put the cursor up at the top where you can type
or edit the cell contents, the cursor is not immediately to the right of the
last character, rather it is a few spaces over to the right as if after the
last character in the city name someone hit the spacebar a few times.  For
example:

Instead of seeing Fesno|

I see Fresno     |

This causes a problem when I do the mail merge because the labels will
insert these spaces so my labels look like this:

Fesno     , CA 93722

Is there a way in Excel to quickly delete these hidden spaces from the cells
so my labels will look OK?  Thanks.

Mark
xLBaron - 30 Jun 2006 15:00 GMT
Try this:

1st -

Add this marco:

Sub Remove_Spaces_On_Right()

Dim X As Range
Dim MyRng As Range
On Error Resume Next
Set MyRng = Selection.SpecialCells(xlCellTypeConstants, xlTextValues)
For Each X In Intersect(Selection, MyRng)
X.Value = RTrim(X.Value)
Next X

End Sub

2nd - Hightlight area that you want to remove space and then run macro.

Regards ...
Mark Christensen - 30 Jun 2006 15:08 GMT
Perfect - thank you!!!

> Try this:
>
[quoted text clipped - 17 lines]
>
> Regards ...
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.