delete ghost spaces

[J.W. Aldridge]

Got this code....

Doesnt always work for some reason. Any alternate way to put this? (Or
is something wrong with it?)


Sub eat_spaces()

Dim c As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each c In Range("G2:T1500")

c = Replace(c, " ", "")
Next


Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[Rick Rothstein]

I'm going to guess some of your spaces are non-breaking spaces (usually
acquired from webpage text), so try using this line of code inside your loop
(instead of the one you posted) and see if it works for you...

c = Replace(Replace(c, " ", ""), Chr(160), "")

 

[Mike H]

Hi,

maybe they aren't spaces, try this. Your function would also remove internal
spaces so if that's what you want put it into the code

c.Value = Replace(c.Value, " ", "")


Sub eat_spaces()
Dim c As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each c In Range("G2:T1500")
c.Value = WorksheetFunction.Trim(c.Value)
c.Value = WorksheetFunction.Clean(c.Value)
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Joe User]

c.Value = WorksheetFunction.Trim(c.Value)
c.Value = WorksheetFunction.Clean(c.Value)

The CLEAN function removes only the first 32 nonprinting characters (codes
0-31). It might be prudent to use CLEAN. But I suspect, as Rick does, that
the culprit is the so-called nonbreaking space (code 160) that frequently
arises when pulling data from web pages. That requires the use of some
replacement operation, such as Excel SUBSTITUTE and VBA Replace. In the
Unicode character, there are additional nonprinting characters among codes
128-255.

See the article at
http://office.microsoft.com/en-us/excel/HP030561311033.aspx .

There is probably some regular expression method that would replace all of
these nonprinting characters in a single pass. I cannot take the time to
look into that myself right now.

Also note that Excel TRIM reduces multiple interstitial spaces to one space.
It does not remove all interstitial spaces, as JW's Replace function does.
Only JW can decide which is the correct operation for his/her purposes.


----- original message -----

 

[ryguy7272]

Try one of these:
Sub Remove_CR_LF()
With Selection
..Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
..Replace What:=Chr(13) & Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
..Replace What:=Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End With
End Sub

Sub Remove_CR_LF()
With Selection
..Replace What:=Chr(39), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
..Replace What:=Chr(146) & Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
..Replace What:=Chr(180), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End With
End Sub

 

[J.W. Aldridge]

thanx!