software

I have exported an Outlook address book to a csv file amp; 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, quot;Aquot;).End(xlUp).Row, 1))
For Each r In Rng
r.Value = Application.Substitute(Trim(CStr(r.Value)), Chr(10), quot;quot;)
Next r

End SubAny help would be greatly appreciated. TIA
--
Tony

Have you tried this?:

Select the cells with carriage returns (char 10's)
lt;Editgt;lt;Replacegt;
Find what: Hold down the [Alt] key, type 0010 then release the [Alt] key
Replace with: ~
Click the [Replace All] button

That should replace all of the carriage returns in the selected cells with
tildes

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Proquot;TonyLquot; wrote:

gt; I have exported an Outlook address book to a csv file amp; have opened the file
gt; in Excel. The address field is a multiline field with Chr(10) designating a
gt; new line. I am trying to convert from multiline to separate fields. Each
gt; line is a new field. I am trying to replace the Chr (10) to a ~ then I can
gt; format into separate fields using the Text to Columns function.
gt;
gt; My problem is that I cannot see how to replace the Chr(10)
gt;
gt; I have seen the following macro in a previous post which I modified but this
gt; does not replace the Chr(10) but adds the ~ after the Chr(10)
gt;
gt; Sub CharacterReturn()
gt; '
gt; ' CharacterReturn Macro
gt; '
gt; 'removes carriage returns from A1 down
gt; Dim Rng, r As Range
gt; Set Rng = Range(Cells(1, 1), _
gt; Cells(ActiveSheet.Cells(Rows.Count, quot;Aquot;).End(xlUp).Row, 1))
gt; For Each r In Rng
gt; r.Value = Application.Substitute(Trim(CStr(r.Value)), Chr(10), quot;quot;)
gt; Next r
gt;
gt; End Sub
gt;
gt;
gt; Any help would be greatly appreciated. TIA
gt; --
gt; Tony

try thistinyurl.com/zkcga
--
Don Guillett
SalesAid Software

quot;TonyLquot; gt; wrote in message
...
gt;I have exported an Outlook address book to a csv file amp; have opened the
gt;file
gt; in Excel. The address field is a multiline field with Chr(10) designating
gt; a
gt; new line. I am trying to convert from multiline to separate fields. Each
gt; line is a new field. I am trying to replace the Chr (10) to a ~ then I
gt; can
gt; format into separate fields using the Text to Columns function.
gt;
gt; My problem is that I cannot see how to replace the Chr(10)
gt;
gt; I have seen the following macro in a previous post which I modified but
gt; this
gt; does not replace the Chr(10) but adds the ~ after the Chr(10)
gt;
gt; Sub CharacterReturn()
gt; '
gt; ' CharacterReturn Macro
gt; '
gt; 'removes carriage returns from A1 down
gt; Dim Rng, r As Range
gt; Set Rng = Range(Cells(1, 1), _
gt; Cells(ActiveSheet.Cells(Rows.Count, quot;Aquot;).End(xlUp).Row, 1))
gt; For Each r In Rng
gt; r.Value = Application.Substitute(Trim(CStr(r.Value)), Chr(10), quot;quot;)
gt; Next r
gt;
gt; End Sub
gt;
gt;
gt; Any help would be greatly appreciated. TIA
gt; --
gt; Tony