Best way to separate .VCF (Contacts) file into appropriate columns?

[gravanoc]

I don't know how to put this to a proper title.

I have a .VCF (contacts) file that needs to be properly separated into eleven columns. I have been trying Power Query to do this because it originally is one big column that is delimited by a colon, but PQ has the ability to only use the left-most colon, which was important here because of website data being included in the file.

The problem is that it seems to be stacked like multiple tables on top of each other. I could transpose the top table correctly, but would then need to do the same for each subsequent block. Here's what it looks like, btw:

What would be a good formula or PQ method for rearranging this?
Note: X-Posted to Reddit Excel Forum, same title.

https://www.reddit.com/r/excel/comments/pjsq6n

 

[Trebor76]

Hi gravnoc,

So here three possibilities.

1. Formula Solution
Put this formula in to cell D2...
=INDIRECT("B" & ROW()+(COLUMN()-4)+(5*(ROW()-2)))
...then copy it across to cell I2 and then copy D2:I2 down as may rows as required.

2. Macro Solution

Option Explicit
Sub Macro1()

    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim i As Long, j As Long, x As Long
  
    Application.ScreenUpdating = False
  
    Set ws = ThisWorkbook.Sheets("Sheet1") 'Modify to match to the sheet with the table if necessary.
    Set tbl = ws.ListObjects("Table1")  'Modify to match the to table name on 'ws' if necessary.
    x = 5 'Block of each dataset from 'tbl'
  
    For i = 1 To tbl.ListRows.Count
        j = IIf(j = 0, 2, j + 1)
        Range(tbl.ListColumns("Data").DataBodyRange(i), tbl.ListColumns("Data").DataBodyRange(i + x)).Copy
        ws.Range("D" & j).PasteSpecial Paste:=xlPasteValues, Transpose:=True 'Leave an extra column so the paste does not become part of the table range
        Application.CutCopyMode = False
        i = i + x
    Next i
  
    Application.ScreenUpdating = True
  
End Sub

3. Power Query Solution
Refer JGordon11's #6 post from here as cdmurray's request is almost identical to yours. I not great with Power Query but I'm sure they will be able to help you if needed.

Regards,

Robert