How to extract the last part of the string in Excel after the last underscore

Question

I have the following example data:

1. animated_brand_300x250
2. animated_brand_300x600
3. customaffin_greenliving_solarhome_anim_outage_offer

How to extract the string from the last underscore in Microsoft Excel?

I want to extract the value before the first underscore and after the last underscore.

First underscore:

=LEFT(B6,SEARCH(“_”,B6)-1)

would return animated and customaffin as output.

How to return the string after the last underscore?

Answer 1

Some other options could be:

=TRIM(RIGHT(SUBSTITUTE(A1,"_",REPT(" ",100)),100))

Or using FILTERXML, being theoretically a better option:

=FILTERXML("<t><s>"&SUBSTITUTE(A1,"_","</s><s>")&"</s></t>","//s[last()]")

An ExcelO365 exclusive method could even be to use XLOOKUP:

=REPLACE(A1,1,XLOOKUP("_",MID(A1,SEQUENCE(LEN(A1)),1),SEQUENCE(LEN(A1)),,0,-1),"")

Or:

=RIGHT(A1,MATCH("_",MID(A1,SEQUENCE(LEN(A1),,LEN(A1),-1),1),0)-1)