Formula to extract text after specific word and before a comma

I have an Excel sheet with a column of cells containing data like the below:

brand=Nike,size=Large,color=Red

I would like to extract the text after 'brand=' and before the ','

In the above example the formula would extract the word 'Nike'.

I have tried text to columns, but unfortunately the data isn't always in the same order (e.g. size= or color= may come first in the cell).

The brand name may be different lengths (e.g. Nike, Reebok, Under Armour etc.).

My current formula is giving me a blank:

=LEFT(A2, SEARCH("brand=",A2)-1)

Can someone please provide a formula to extract this data?

2 Answers

Try to use FILTERXML function of which available in Excel 2013 or above.

In B2, formula copied down :

=FILTERXML("<a "&SUBSTITUTE(SUBSTITUTE(A2,"=","='"),",","' ")&"'/>","a/@brand")

edit :

Change the formula specific word "brand", to other word like "size" or "color", it will extract the text after the specific word

The first problem is that your SEARCH is returning "1", I.e. the "brand" appears at the first point in your string, and that the left only returns that number of characters. Following this route what you want to do is add the length of the "brand" to string, then do another search for the comma and add that too.

Not knowing where the "brand" is in your string you first need to crop everything before that point.

So, starting from SEARCH("brand=",A2) we trim the start of the string with RIGHT:

=RIGHT(A2,LEN(A2)-SEARCH("brand=",A2)+1)

We use the LEN so that we get the full string length minus the position that "brand" starts at.

This gives us a string starting with "brand=".

We can then optimise the RIGHT by changing the value of our +1 to -5 to trim out the "brand="

=RIGHT(A2,LEN(A2)-SEARCH("brand=",A2)-5)

From here it gets messy as we need to find the first , at the same time as we are trying to trim the string. A helper cell might be cleaner and faster, but you can (for small spreadsheets) just use the same formula above twice. That gives us just one last LEFT and a final SEARCH:

=LEFT(RIGHT(A2,LEN(A2)-SEARCH("brand=",A2)-5), SEARCH(",",RIGHT(A2,LEN(A2)-SEARCH("brand=",A2)-5))-1)

There are probably much cleaner ways to do this, this is just one way.