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?

JvdV · Answer 1 · 2020-08-04T13:41:16.593

7

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)

edited Aug 04 '20 at 13:41

answered Aug 04 '20 at 07:01

JvdV

70,606
8
39
70

1

Very elegant solutions - especially the XML. – Michael Wycisk Aug 04 '20 at 09:12
1

@MichaelWycisk, thanks. You might find [this](https://stackoverflow.com/q/61837696/9758194) interesting if you like to know more about `FILTERXML`. – JvdV Aug 04 '20 at 09:26
1

Thanks, that's interesting. I actually thought that it would be possible to extract elements by position using `FILTERXML` - but only after reading your answer here. – Michael Wycisk Aug 04 '20 at 09:45

score 6 · Accepted Answer · answered Aug 03 '20 at 23:27

You can find the string after the last _ using the RIGHT function. The tricky part is to find the position of the last _.

First, we need to determine, how many _ there are. This can be done be removing all _ and compare the length of both strings:

LEN(A1)-LEN(SUBSTITUTE(A1;"_";""))

Since we now know that it is the second occurrence of _ that we have to look for, we can use the SUBSTITUTE function to replace the second occurrence of _ with another character (this has to be a character that is not part of your original string - I have chosen#).

Now, we use the FIND function to search for the position of the # character. This position can now be delivered to the RIGHT function.

Your final formula will be:

=RIGHT(A1;LEN(A1)-FIND("#";SUBSTITUTE(A1;"_";"#";LEN(A1)-LEN(SUBSTITUTE(A1;"_";"")));1))

@GerhardPowell Correct, depending on your Windows regional settings, you might need to use either comma `,` or colon `;` as list separator. — Michael Wycisk, Feb 05 '22 at 12:26

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

2 Answers2

Linked

Related