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
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.
"Joe User" wrote:
> "Mike H" wrote:
> > 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/ex...561311033.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 -----
>
> "Mike H" wrote:
> > 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.
> >
> >
> > "J.W. Aldridge" wrote:
> >
> > > 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
> > > .
> > >
Similar Threads
