How to split this string into separate column in spreadsheet?

Question

I have the following set of data after download it from the web. The original data is supposed to be Type is "preference", Properties is "Create, Filter, Group, Nillable, Sort, Update" and Description is "ID".

TypereferencePropertiesCreate, Filter, Group, Nillable, Sort, UpdateDescriptionID
TypereferencePropertiesCreate, Filter, Group, Nillable, Sort, UpdateDescriptionID of the account associated with this opportunity.
TypecurrencyPropertiesCreate, Filter, Nillable, Sort, UpdateDescriptionEstimated total sale amount.

There are online tutorials to teach how to split string by using delimiter such as ", space, or other symbols" in spreadsheet but my set of data is different. How can I split "Type", "Properties" and "Description" out?

Answer 1

If there is not a delimiter string, (@Jeeped mentions a zero-width space), then you can use ordinary string functions, like Mid and Instr to parse this out. I put the output on a new worksheet:

Sub foo()
    Dim findType As Integer
    Dim findProperties As Integer
    Dim findDescription As Integer
    Dim rng As Range
    Dim r As Range
    Dim i As Integer
    Dim newSheet

    Set rng = Range("A1:A3")

    'Add a new sheet and put some header rows on it
    Set newSheet = Sheets.Add
        newSheet.Range("A1").Value = "Type"
        newSheet.Range("B1").Value = "Properties"
        newSheet.Range("C1").Value = "Description"
        i = 1
    For Each r In rng.Cells
        findType = InStr(1, r.Value, "Type")
        findProperties = InStr(1, r.Value, "Properties")
        findDescription = InStr(1, r.Value, "Description")

        '## Print some output values to a new worksheet

        With newSheet
            i = i + 1
            .Range("A" & i).Value = Mid(r.Value, findType + 4, findProperties - (findType + 4))
            .Range("B" & i).Value = Mid(r.Value, findProperties + 10, findDescription - (findProperties + 10))
            .Range("C" & i).Value = Mid(r.Value, findDescription + 11)
        End With
    Next

End Sub

Here is an alternate that puts it on the same worksheet (untested):

Sub foo2()
    Dim findType As Integer
    Dim findProperties As Integer
    Dim findDescription As Integer
    Dim typeStr as String, propStr as String, descStr as String

    Dim rng As Range
    Dim r As Range
    Dim i As Integer

    Set rng = Range("A1:A3")

    i = 1
    For Each r In rng.Cells
        findType = InStr(1, r.Value, "Type")
        findProperties = InStr(1, r.Value, "Properties")
        findDescription = InStr(1, r.Value, "Description")

        i = i + 1
        typeStr = Mid(r.Value, findType + 4, findProperties - (findType + 4))
        propStr = Mid(r.Value, findProperties + 10, findDescription - (findProperties + 10))
        descStr = Mid(r.Value, findDescription + 11)
        rng.Value = typStr
        rng.Offset(0,1).Value = propStr
        rng.Offset(0,2).Value = descStr
    Next
    Range("A1").EntireRow.Insert
    Range("A1").Value = "Type"
    Range("B1").Value = "Properties"
    Range("C1").Value = "Description"
End Sub