Need help parsing a string

[Daminc]

I've exported a file from Outlook into excel and used this code:


Code:
--------------------
Sub removelinebreaks()

Dim lastrow As Long, i As Long
Dim first As String, second As String

lastrow = Cells(Rows.Count, 2).End(xlUp).Row
For i = 2 To lastrow

first = Cells(i, "B").Select
ActiveCell.Value = WorksheetFunction. _
Substitute(ActiveCell.Value, Chr(10), Chr(0))
Next i

End Sub
--------------------


To get rid of any line spaces and I'm left with:


Code:
--------------------
35084#http://www.asite.co.uk#[email protected]
--------------------


I would like to be able to parse this into 3 different columns if I
could.

Can anyone help me with this please?

 

[Mike Fogleman]

Under the Data menu use Text to Columns. You will want Delimited and then
select Other and type in #. Click Finish.
Very similar to the text import wizard except your text is already imported.
Mike F

 

[Daminc]

I followed that but it didn't work as I expected.

There are nearly 2500 rows.

I selected them all and followed the instructions.
All it did was delete everything apart from the 3 numbers on lines 6,
and 8.
I thought the formatting might interfere so I formatted all the cell
to text but still no joy.

It even shows this in the preview panel.

What am I missing :confused

 

[Daminc]

Solved it

I had deleted the 'new lines' but so some reason it was reading a
'carriage return' so I created this little macro:


Code:
--------------------
Sub removelinebreaks()

Dim lastrow As Long, i As Long
Dim first As String, second As String

lastrow = Cells(Rows.Count, 2).End(xlUp).Row
For i = 2 To lastrow

first = Cells(i, "B").Select
ActiveCell.Value = WorksheetFunction. _
Substitute(ActiveCell.Value, Chr(10), Chr(0))
ActiveCell.Value = WorksheetFunction. _
Substitute(ActiveCell.Value, Chr(13), Chr(0))
Next i

End Sub
--------------------


and then applied what you said and it works now.

Thanks Mike.

 

[Daminc]

Solved it

I had deleted the 'new lines' but so some reason it was reading a
'carriage return' so I created this little macro:


Code:
--------------------
Sub removelinebreaks()

Dim lastrow As Long, i As Long
Dim first As String, second As String

lastrow = Cells(Rows.Count, 2).End(xlUp).Row
For i = 2 To lastrow

first = Cells(i, "B").Select
ActiveCell.Value = WorksheetFunction. _
Substitute(ActiveCell.Value, Chr(10), Chr(0))
ActiveCell.Value = WorksheetFunction. _
Substitute(ActiveCell.Value, Chr(13), Chr(0))
Next i

End Sub
--------------------


and then applied what you said and it works now.

Thanks Mike.