0

I've got an Excel spreadsheet with columns containing product attributes formatted this way:

ca_colour=WHITE,ca_min_order_qty=4,ca_season=SS17,ca_sentiment=Inspiration,ca_type=Candles

I've got hundreds of products for which I need to extract e.g. value of ca_season and I'm really struggling with figuring this one out. Different products might have ca_season in a different place of this cell.

I would appreciate any help with writing a formula for this search. Thank you.

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
Lukasz
  • 15
  • 2

2 Answers2

1

FILTERXML will give desired result. Try-

=FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s[starts-with(., 'ca_season')]")

enter image description here

If you are not using Excel365 then user FILTERXML() with INDEX() function.

=INDEX(FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s[starts-with(., 'ca_season')]"),1)

As @JvdV mentioned in comment you may want only product code then use like-

=REPLACE(FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s[starts-with(.,'ca_season=')]"),1,10,"")

And a fantastic tips & tricks on FilterXML() here from JvdV

BigBen
  • 46,229
  • 7
  • 24
  • 40
Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • Nice, beaten me to it haha. You might want to use: `=REPLACE(FILTERXML(""&SUBSTITUTE(A1,",","")&"","//s[starts-with(.,'ca_season=')]"),1,10,"")`. Though, you don't need to use `INDEX()` in previous versions of Excel due to implicit intersection. – JvdV Jan 12 '21 at 11:08
  • Thanks, both of you that's very helpful. I wish I could say I understand anything of these formulas. Have a great day! – Lukasz Jan 12 '21 at 11:16
  • Sorry, one more question is there any way of making this formula more generic let's say can I put e.g ca_colour in a different cell and use this cell's reference instead of typing ca_colour into a formula? @JvdV same with your formula can I make it more generic to only retrieve what's after the "=" or do I have to adjust number of characters every time I need to extract a different value. Thanks for your patience guys. – Lukasz Jan 12 '21 at 11:31
  • @Lukasz Ask that as a separate question. – Ian Kemp Jan 15 '21 at 12:59
1

Alternatively, if one has Excel 365, try:

enter image description here

Formula in B1:

=LET(X,FIND("ca_season=",A1)+10,MID(A1,X,FIND(",",A1&",",X)-X))

More generic:

enter image description here

=LET(X,FIND(C1,A1)+LEN(C1)+1,MID(A1,X,FIND(",",A1&",",X)-X))
JvdV
  • 70,606
  • 8
  • 39
  • 70