Trying to parse data that has line breaks

Bfish · Oct 4, 2010

I am trying to parse some data into seperate colums either by using a macro or some VBScript but I'm not sure how to go about doing it. Currently my data looks like this.
Column A
John Smith Company
Contact Person: John Smith
Ste 555
5500 Main St
Kansas City, MO 64112
Phone: (555) 555-5555
Fax: (555) 555-5555
Email: john.smith@johnsmith.com
Website: www.johnsmith.com

I want it to look like this.
<TABLE style="WIDTH: 911pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1213 x:str><COLGROUP><COL style="WIDTH: 141pt; mso-width-source: userset; mso-width-alt: 6875" width=188><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4169" width=114><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 5376" width=147><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1536" width=42><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1572" width=43><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><COL style="WIDTH: 176pt; mso-width-source: userset; mso-width-alt: 8594" width=235><COL style="WIDTH: 127pt; mso-width-source: userset; mso-width-alt: 6180" width=169><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 141pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 width=188>Column A</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 86pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=114>Column B</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 110pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=147>Column C</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 61pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=81>Column D</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 32pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=42>State</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 32pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=43>Zip</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=100>Phone</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 71pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=94>Fax</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 176pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=235>email address</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 127pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=169>Website</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>John Smith company</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">John Smith</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Ste 555, 5500 Main St</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Kansas City</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">MO</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>64112</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">(555) 555-5555</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">(555) 555-5555</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24>john.smith@john.smith.com</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24>www.johnsmith.com</TD></TR></TBODY></TABLE>

When it is all bunched in the one column each line is seperated by a line break (displayed in excel as a little square box). Can I seperate the data by using a macro or do I have to use VBScript and if it is with VB where do I put it?

Any help would be greatly appreciated. Thanks!!

Ruddles · Oct 4, 2010

Is it always exactly nine lines: name, contact, room, address, city/state/zip, phone, fax, email, Web site?

Bfish · Oct 4, 2010

From what I can tell yes. But I see this with excel a lot so it would be nice to have a solution in case the data does not stay consistent as well.

Ruddles · Oct 4, 2010

In a copy of your workbook, press Alt-F11 to open Microsoft Visual Basic, press Ctrl-R to view the Project Explorer, then double-click the name of your worksheet. You should now find the cursor just below the words Option Explicit. Paste this in at that point so you end up with this:-

Code:

Option Explicit
 
Public Sub SplitCell()
 
  Const MaxLines As Integer = [COLOR=red][B]15[/B][/COLOR]
  
  Dim iPtr As Long
  Dim iCol As Integer
  Dim sTemp As String
  
  iPtr = 1
  Do While Not IsEmpty(Cells(iPtr, 1))
    sTemp = Cells(iPtr, 1) & String(MaxLines, Chr(10))
    For iCol = 2 To MaxLines + 1
      Cells(iPtr, iCol) = Left(sTemp, InStr(sTemp, Chr(10)) - 1)
      sTemp = Mid(sTemp, InStr(sTemp, Chr(10)) + 1)
    Next iCol
    iPtr = iPtr + 1
  Loop
  
End Sub

Back to the worksheet and go Developer > Macros > SplitCell > Run.

If your record has more than fifteen lines of data, change the bit in red.

Let me know how it goes.

shg · Oct 4, 2010

Another way:

Code:

Sub x()
    Application.DisplayAlerts = False
    With Intersect(ActiveSheet.UsedRange, Columns("A"))
        .TextToColumns Destination:=Range("A1"), _
                       DataType:=xlDelimited, _
                       Tab:=False, _
                       Semicolon:=False, _
                       Comma:=False, _
                       Space:=False, _
                       Other:=True, _
                       OtherChar:=vbLf
    End With
    Columns.AutoFit
    Rows.AutoFit
End Sub

Bfish · Oct 4, 2010

Ok well I'm a little farther along. After running that script it did parse the data into seperate columns. It looks like for the most part the first two columns stayed consistent (company name, contact name) but after that it looked like there were some variations. On occaision the web site is in the column that would be for phone or for fax, so I will have to work on that some. But this is a lot closer to where i want to be when I started. Thanks so much for your help!

Bfish · Oct 4, 2010

I ran your script and it got rid of everything except for the company name. But it did get rid of the line break at the end of it.

Ruddles · Oct 5, 2010

Bfish said:
It looks like for the most part the first two columns stayed consistent (company name, contact name) but after that it looked like there were some variations. On occaision the web site is in the column that would be for phone or for fax...

Was this for my solution? If so, I can arrange it so that lines which start with a recognisable word (Phone/Fax/Email/Website) followed by a colon are always placed in specified columns. I could even do that for a line ending in comma-space-state-space-zipcode (like "Kansas City, MO 64112").

Bfish · Oct 5, 2010

Ruddles said:
Was this for my solution? If so, I can arrange it so that lines which start with a recognisable word (Phone/Fax/Email/Website) followed by a colon are always placed in specified columns. I could even do that for a line ending in comma-space-state-space-zipcode (like "Kansas City, MO 64112").

Yes that was for your solution. That would be great. At looking at the data on my sheet it looks like something along that lines would work. All phone numbers look like they start with phone: and fax's start with fax: so that would probably work well with this sheet. Also was that script supposed to get rid of the square box representing the line break? Cause I still see those at the end of the parsed data.

Ruddles · Oct 5, 2010

Try this:-

Code:

Option Explicit
 
Public Sub SplitCell()
 
  Const MaxLines As Integer = 15
 
  Dim iPtr As Long
  Dim iCol As Integer
  Dim sTemp As String
  Dim sField As String
 
  iPtr = 1
  Do While Not IsEmpty(Cells(iPtr, 1))
    sTemp = Cells(iPtr, 1) & String(MaxLines, Chr(10))
    For iCol = 2 To MaxLines + 1
      sField = Left(sTemp, InStr(sTemp, Chr(10)) - 1)
      If Len(sField) = 0 Then Exit For
      Select Case True
        Case IsCity(sField): Cells(iPtr, MaxLines - 4) = sField
        Case Left(sField, 6) = "Phone:": Cells(iPtr, MaxLines - 3) = sField
        Case Left(sField, 4) = "Fax:": Cells(iPtr, MaxLines - 2) = sField
        Case Left(sField, 6) = "Email:": Cells(iPtr, MaxLines - 1) = sField
        Case Left(sField, 8) = "Website:": Cells(iPtr, MaxLines) = sField
        Case Else: Cells(iPtr, iCol) = sField
      End Select
      sTemp = Mid(sTemp, InStr(sTemp, Chr(10)) + 1)
    Next iCol
    iPtr = iPtr + 1
  Loop
 
End Sub
 
Private Function IsCity(ByVal argString As String) As Boolean
 
  IsCity = False
 
  If Len(argString) < 10 Then Exit Function ' too short to contain a city, state and zip code
 
  argString = UCase(Right(argString, 10))
  If Left(argString, 2) <> ", " Then Exit Function ' no comma separating city from state
 
  argString = Right(argString, 8)
  If Left(argString, 1) < "A" Then Exit Function
  If Left(argString, 1) > "Z" Then Exit Function ' first character of state not a letter
 
  argString = Right(argString, 7)
  If Left(argString, 1) < "A" Then Exit Function
  If Left(argString, 1) > "Z" Then Exit Function ' second character of state not a letter
 
  argString = Right(argString, 6)
  If Left(argString, 1) <> " " Then Exit Function ' no space between state and zip code
 
  argString = Right(argString, 5)
  If Not IsNumeric(argString) Then Exit Function ' last five characters not numeric, so not a zip code
 
  IsCity = True
 
End Function

Report any problems!

Trying to parse data that has line breaks

Bfish

New Member

Excel Facts

Ruddles

Well-known Member

Bfish

New Member

Ruddles

Well-known Member

shg

MrExcel MVP

Bfish

New Member

Bfish

New Member

Ruddles

Well-known Member

Bfish

New Member

Ruddles

Well-known Member

Similar threads

Forum statistics

Share this page

Trying to parse data that has line breaks

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

MrExcel MVP

New Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock