Remove Carriage return and Line Feed

[woodyinoz]

Hi all,

I am pulling some data out of SQL Server and into Excel but I am getting some annoying Carriage Returns and Lin Feeds in some of my fields....

I know that Carriage Return is Chr(13) and that Line Feed is Chr(10) but how can I write some code to get rid of these throughout my spreadsheet?

Thanks in advance,

Woody

 

[TGandon]

VB6 has a "Replace" function but VBA appears to have not so add this function to your code

Private Function Replace(ByVal OriginalText As String, _
                         ByVal FindText As String, _
                         ByVal ReplaceText As String) As String

Dim strText As String

If FindText <> "" Then
   If InStr(1, ReplaceText, FindText, vbTextCompare) = 0 Then
      strText = OriginalText
      Do While InStr(strText, FindText)
         strText = Left(strText, InStr(strText, FindText) - 1) & _
                   ReplaceText & _
                   Mid(strText, InStr(strText, FindText) + Len(FindText))
      Loop
      Replace = strText
   End If
End If

End Function

You can then use it (for example) thus

'Remove carriage returns
MyData = Replace(MyData, vbCr, "")
' Replace carriage return + linefeed with space
MyData = Replace(MyData, vbCrLf, " ")
' Replace linefeeds with tabs
MyData = Replace(MyData, vbLf, vbTab)

Note
vbCr is direct replacement for Chr(13)
vbLf is direct replacement for Chr(10)
vbCrLf is direct replacement for Chr(13) & Chr(10)
vbTab is direct replacement for Chr(9)

Trevor

 

[GVF]

Application.WorksheetFunction.Clean" will remove non-printing characters (such as CR's and LF's) from your data as you bring it in, or you can do a cells.replace and remove the characters after the data has been added to the worksheet. Help for the Clean function is in the Excel help files.

ActiveCell.Value = Application.WorksheetFunction.Clean(TheDataYouBringIn)

or

CR = Chr(13)
Cells.Replace What:=CR, Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False
LF= Chr(10)
Cells.Replace What:=LF, Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False

Greg