Need help parsing a string
 

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#
--------------------


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

Can anyone help me with this please?


--
Daminc
------------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=505254

Need help parsing a string
 

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

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#
--------------------


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

Can anyone help me with this please?


--
Daminc
------------------------------------------------------------------------
Daminc's Profile:
http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=505254

Need help parsing a string
 

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

--
Damin
-----------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...fo&userid=2707
View this thread: http://www.excelforum.com/showthread.php?threadid=50525

Need help parsing a string
 

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
------------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=505254

Need help parsing a string
 

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
------------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=505254