Transpose irregular data from one column into rows

[pirunner]

I have a large dataset of business information all contained in column A that is formatted like this, with each line in it's own, single cell:

Company Name 1
123 E Main St
New York, NY 12345-1234 | Map
Phone: (555) 555-5555
Type: LLC
www.CompnayOne.com

Company Name 2
Sacramento, CA
Phone: (444) 444-4444
Type: Partnership
www.CompnayTwo.com

Company Name 3
456 E Main St
Mesa, AZ 12345
Type: Startup
Company Name 4
999 E Center St
Chicago, IL 12345 | Map
Phone: (333) 333-3333
Type: LLC

There is some regularity in formatting and the number of rows per business, but not really anything terribly useful. The MAX amount of information presented for any given business is as shown for Business 1 (6 lines: name, address line 1, address line 2, phone, type, website), BUT many businesses are missing one or more of these lines. Rarely, only the name is present with nothing else. Furthermore, sometimes an export error resulted in there NOT being a blank row between two business (e.g. as shown between Businesses 3 & 4 here).

My goal is to have a new sheet with one row per business, and one column for each of name, address line 1, address line 2, phone, type, and website. Businesses with no information for a given column will just have a blank cell.

This solution works well if the data for each business is of regular length and regularly spaced, but that won't work here.

This solution is a bit closer. It works well regardless of the amount of data per business, but relied on there being a space between each business, so that's still not great for this problem. Also, while this solution gets each business into its own row, analogous data does not all end up in the same column.

There are ~4,000 total businesses in this spreadsheet (ie > 20,000 total rows of data).

I'm at a loss. If need be, I could go through and manually insert blank rows between business that don't have one, but that still doesn't get me analogous data in separate columns.

Help!?
Thanks!

 

[BGDunbar]

I'm working on something similar but with only three pieces of data. I was able to identify that my missing piece of data was always the phone number. I wanted to insert a blank row where no phone number existed. I got two replies that both worked. I am no where near good enough to know if they will work for you but here they are.

JLGWhiz gave me the following:

Sub addRow()
Dim i As Long, lr As Long
With ActiveSheet
    lr = .Cells(Rows.Count, 1).End(xlUp).Row
    For i = lr To 3 Step -1
        If InStr(.Cells(i, 1), ".") > 0 Then
            If Len(Left(Trim(.Cells(i, 1)), InStr(.Cells(i, 1), "."))) <= 3 Then
                If Not Cells(i - 1, 1) Like "???-???-????" Then
                    Rows(i).Insert
                End If
            End If
        End If
    Next
End With
End Sub

Then Rick Rothstein gave me a different approach:

Sub InsertBlanksForMissingPhoneNumbers()
  Dim R As Long, LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  For R = LastRow To 2 Step -1
    If Trim(Cells(R, "A").Value) Like "#.*" Or Trim(Cells(R, "A").Value) Like "##.*" Then
      If Not Cells(R - 1, "A").Value Like "*-*-*" Then Rows(R).Insert
    End If
  Next
End Sub

Hope this helps at least a little.