How to extract excel cell values delimited with filters?

Question

In the each cell in a column I have this information in the cells:

A1 values:

Depth=standard;Size=1 section;Doors=hinged solid;Interior configuration=shelves;Compressor HP=1/2 HP;Interior finish=stainless steel;Exterior finish=stainless steel;Refrigeration=top mount self-contained

A2 values:

Top openings= 6 pan;Size=1 section;Compressor HP=1/6 HP;Style=drawers;Exterior finish=stainless steel;Interior finish=stainless steel;Refrigeration=rear mounted

A3,A4,A5 etc all follow similar formats

I need some method of abstracting out the following information into its own cells:

I need each semicolon separated value to be checked if there is a column name for it already, if not, make a new column and put all corresponding values where they need to be

I thought about using text->columns and then using index/match but I haven't been able to get my match criteria to work correctly. Was going to do this for each unique column. Or do I need to use VBA?

Answer 1

My solution below works as intended but the data wasn't as formatted as I originally thought.

Option Explicit

Private Sub Auto_Open()

MsgBox ("Welcome to the delimiter file set.")


End Sub

'What this program does:
'http://i.imgur.com/7MVuZLt.png

Sub DelimitFilter()

Dim curSpec As String
Dim curSpecArray() As String
Dim i As Integer, IntColCounter As Integer, iCounter As Integer, argCounter As Integer
Dim WrdString0 As String, WrdString1 As String
Dim dblColNo As Double, dblRowNo As Double

Worksheets(1).Activate

'Reference to cell values that always have data associated to them
Range("W2").Activate

'checks for number of arguments to iterate through later
Do

    If ActiveCell.Value = "" Then Exit Do
    ActiveCell.Offset(1, 0).Activate
    argCounter = argCounter + 1

Loop

'Check # of arguments
Debug.Print (argCounter)

'Values to delimit
Range("X2").Activate
IntColCounter = 1

'Loop each row argument
For iCounter = 0 To argCounter

    'Set var to activecell name
    dblColNo = ActiveCell.Column
    dblRowNo = ActiveCell.Row

    'Grab input at active cell
    curSpecArray() = Split(ActiveCell.Value, ";")

    'Ignore empty rows
    If Not IsEmpty(curSpecArray) Then

        'Iterate every delimited active cell value at that row
        For i = LBound(curSpecArray) To UBound(curSpecArray)

            'Checks for unique attribute name, if none exists, make one
            WrdString0 = Split(curSpecArray(i), "=")(0)

            'a large range X1:ZZ1 is used as there are many unique column names
            If IsError(Application.Match(WrdString0, Range("X1:ZZ1"), 0)) Then  'if NOT checks if value exists
                Cells(1, dblColNo + IntColCounter).Value = WrdString0
                IntColCounter = IntColCounter + 1
            End If

            'Output attribute value to matching row and column
            WrdString1 = Trim(Split(curSpecArray(i), "=")(1))
            Debug.Print (WrdString1)
            Cells(dblRowNo, -1 + dblColNo + Application.Match(WrdString0, Range("X1:ZZ1"), 0)).Value = WrdString1


        Next i

    End If

    'Iterate Next row value
    ActiveCell.Offset(1, 0).Activate

Next iCounter

End Sub