Menu

Fixing multiline cell

Search this Thread

Prev

Previous Post

Next Post

Next

#3

Report Post

Posted to microsoft.public.excel.worksheet.functions

Don Guillett

Posts: n/a

Default

Fixing multiline cell

try thishttp://tinyurl.com/zkcga
--
Don Guillett
SalesAid Software

"TonyL" wrote in message
...
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

Reply With Quote

Reply With Quote

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Posting Rules
Smilies are On [IMG] code is On HTML code is Off Trackbacks are On Pingbacks are On Refbacks are On

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
why a reference to an empty cell is not considered empty	Nicoscot	Excel Discussion (Misc queries)	10	March 10th 06 05:36 AM
Adding a row to worksheet does not update cell references in another.	blausen	Excel Worksheet Functions	5	February 25th 06 09:14 PM
Urgent date/scheduling calc needed	jct	Excel Worksheet Functions	3	February 24th 06 01:36 AM
Transfer Cell Formatting for linked cells	Scott	Excel Discussion (Misc queries)	2	November 23rd 05 11:04 PM
cell color index comparison	MINAL ZUNKE	New Users to Excel	1	June 30th 05 07:11 AM

All times are GMT +1. The time now is 11:00 PM.

About Us

"It's about Microsoft Excel"

Contact Us ExcelBanter forum home Privacy Statement