Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
String Parsing : Best Methods : VBA | Excel Programming | |||
parsing a string | Excel Programming | |||
Parsing when deliminator is a string | Excel Worksheet Functions | |||
Parsing a String to get Numbers | Excel Programming | |||
Unicode string parsing? Please help! | Excel Programming |