Excel extract value appearing after searched string

Question

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.

Answer 1

Alternatively, if one has Excel 365, try:

Formula in B1:

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

---

More generic:

=LET(X,FIND(C1,A1)+LEN(C1)+1,MID(A1,X,FIND(",",A1&",",X)-X))