Excel: How to extract text within a string of text until separator

Question

I have a simple problem that I hope to resolve with a pretty simple Excel function.

I have a list with multiple rows (all in one column) containing attributes and their corresponding values, each being separated with a |. It basically looks like this:

Color:Red|Size:M|COD:United States|Price:50.84
Color:Blue|Category:Jeans|COD:China|Price:29.95

Now, I want to extract the strings after each attribute in a separate column and only fill the column if a value exists. In the end it should look like this:

LONGFORM	Color	Size	Category	COD	Price
Color:...	Red	M		United States	50.84
Color:...	Second		Jeans	China	29.95

I have found a solution to use following code, but it only works for the first column:

=TEIL(LEFT($A2;MID("|";$A2)-1);FIND(B$1;$A2)+1+LEN(B$1);LEN($A2))

If you know a VBA solution to solve it, it's also appreciated!

Answer 1

One UDF Per Attribute

Create a UDF for each of your 5 categories and just use the formula as you usually would where the input is just your LONGFORM value. Here is a sample for Color. You would create a copy of this for each attribute and replace Color with the appropriate name.

Note: for price you will want to declare the function output as Double instead of String

Public Function Get_Color(Target As Range) As String

Dim i As Long, Temp
Temp = Split(Target, "|")

For i = LBound(Temp) To UBound(Temp)
    If InStr(Temp(i), "Color") Then
        Get_Color = Split(Temp(i), ":")(1)
        Exit Function
    End If
Next i

End Function

---

One UDF With Attribute Parameter

You can also use one UDF across the board and provide two inputs in the formula (LONGFORM & ATTRIBUTE) where attribute is either Color, Size, Category, COD, Price. Ideally you would just set the second parameter equal to the header of interest and drag down. It's worth noting that in current form the attribute parameter is case sensitive typo intolerant.

Public Function Get_Attribute(Target As Range, Element As String)

Get_Attribute = ""
Dim i As Long, Temp
Temp = Split(Target, "|")

For i = LBound(Temp) To UBound(Temp)
    If InStr(Temp(i), Element) Then
        Get_Attribute = Split(Temp(i), ":")(1)
        If IsNumeric(Get_Attribute) Then Get_Attribute = CDbl(Get_Attribute)
        Exit Function
    End If
Next i

End Function

Here is a sample of the second solution and how you would call the function. It would be similar for the One Attribute : One UDF approach except you would call different functions for each column but only give one input