6

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?

Michael Wycisk
  • 1,508
  • 9
  • 20
cyborg
  • 371
  • 1
  • 4
  • 17

2 Answers2

5

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))

Get the last string after a character with multiple occurrrences in Excel

Michael Wycisk
  • 1,508
  • 9
  • 20
5

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)
JvdV
  • 53,146
  • 6
  • 36
  • 60
  • 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