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

user_14700150 0
Viewed 337

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!

extract excel-formula vba excel
3 Answers

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

enter image description here

user_67064190

Adjusting my Answer HERE

With your Labels across the top, Use FILTERXML in B2 and copy over and down:

=IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE($A2,":","|"),"|","</b><b>")&"</b></a>","//b[preceding::*[1]='"&B$1&"']"),"")

enter image description here

user_48515900

This answer will only work as below if you have the LET() function (available in more recent versions of Excel). Otherwise you can use the same logic, but the function will be longer and harder to read.

Formula in cell B3 is:

=LET(v,LET(x,FIND(B$3:F$3,$A4),IF(ISERROR(x),"",LET(s,MID($A4,x,IFERROR(FIND($C$1,$A4,x)-1,LEN($A4))+1-x),RIGHT(s,LEN(s)-LEN(B$3:F$3)-1)))),IFERROR(VALUE(v),v))

Then copy this formula down the rows.

enter image description here

This is an array formula, so takes the row of field headers ("Color","Size" etc) as input, then fills the cells with the formula results. I've generalized it slightly by making the delimiter (here |) as a variable. Number values should be preserved as numbers.

user_138129820
Related