Deleting blanks from the end of values in cells

[Todd Huttenstine]

I have a very big range of data on a worksheet. The range
is located on worksheet Renewals A:F. Another worksheet
Wkly Renewals has a pivot table referencing that range.
Some values in the range may have a space (" ") at the end
of the value and needs to be removed.

For example cell D205 should be "Todd Huttenstine" but
instead it is "Todd Huttenstine " or cell D206 may have
the value "Randy " instead of "randy". I need these
spaces removed with code because my pivot table is showing
duplicates due to this space. The pivot tables looks at
this as 2 different values because technically the space
makes it that way.

Any help is appreciated.

Todd Huttenstine

 

[Frank Kabel]

Hi Todd
have a look at
http://www.mvps.org/dmcritchie/excel/proper.htm

 

[Gord Dibben]

Todd

Sub TRIM_EXTRA_SPACES()
Dim cell As Range
For Each cell In Selection
If (Not IsEmpty(cell)) And _
Not IsNumeric(cell.Value) And _
InStr(cell.Formula, "=") = 0 _
Then cell.Value = Application.Trim(cell.Value)
Next
End Sub

Gord Dibben Excel MVP

 

[Dave Peterson]

Take a look in VBA's help for:

Trim, LTrim, and RTrim

Gord used Application.Trim which is a lot like VBA's trim--except that it gets
rid of extra internal spaces, too. (VBA's Trim just touches the left and right
edges).

application.trim(" Todd Huddlestine ")
and
trim(" Todd Huddlestine ")

Would return different strings.

 

[Dave Peterson]

Sorry about misspelling your name.

(I used to work with a person named Todd Huddlestine. And I think I've always
read your name incorrectly because of this <vbg>.)

That and I suffer a mild case of lesdyxia.