Find and Replace Carriage Return in Excel

[rexwrx]

Hi all,

I need to find all <CR> character (carriage return) because when I export a table from Access to Excel, the data contains "[]" that are carriage returns.

so i need to find these characters and replace with null

can i do this with vba?

many thanks

 

[acw]

Hi

This should cover a CR, LF and CRLF.

Sub bbb()
  Cells.Replace what:=vbCr, replacement:=""
  Cells.Replace what:=vbLf, replacement:=""
  Cells.Replace what:=vbCrLf, replacement:=""
End Sub

Tony

 

[rexwrx]

cool thanks for that.

this one works better with the entire sheet.

Sub ChgInfo()
Dim Sht As Worksheet
For Each Sht In Worksheets
Sht.Cells.Replace What:="old stuff", _
Replacement:="new stuff", LookAt:=xlPart, MatchCase:=False
Next
End Sub