Delete hidden spaces in cells

[Mark Christensen]

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]

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]

Perfect - thank you!!!