I have exported an Outlook address book to a csv file & have opened the file
in Excel. The address field is a multiline field with Chr(10) designating a
new line. I am trying to convert from multiline to separate fields. Each
line is a new field. I am trying to replace the Chr (10) to a ~ then I can
format into separate fields using the Text to Columns function.

My problem is that I cannot see how to replace the Chr(10)

I have seen the following macro in a previous post which I modified but this
does not replace the Chr(10) but adds the ~ after the Chr(10)

Sub CharacterReturn()
'
' CharacterReturn Macro
'
'removes carriage returns from A1 down
Dim Rng, r As Range
Set Rng = Range(Cells(1, 1), _
Cells(ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row, 1))
For Each r In Rng
r.Value = Application.Substitute(Trim(CStr(r.Value)), Chr(10), "")
Next r

End Sub


Any help would be greatly appreciated. TIA
--
Tony