Here we describe, how to get the part of text string, which is after last instance of some string (space, slash...).

Like here:

  • turtle/frog/butterfly

we need butterfly (because it is after the last slash)

How to do it?

For string in A2 use this:

  • =MID(A1;FIND("#";SUBSTITUTE(A1;"/";"#";LEN(A1)-LEN(SUBSTITUTE(A1;"/";""))))+1;100)


  • LEN(A1)-LEN(SUBSTITUTE(A1;"/";"")) - counts characters (slashes). In our example it is 2.
  • SUBSTITUTE(A1;"/";"#";LEN(A1)-LEN(SUBSTITUTE(A1;"/";""))) - replace the last one with #, or any other symbol which is not contained in the original string
  • FIND("#";SUBSTITUTE(A1;"/";"#";LEN(A1)-LEN(SUBSTITUTE(A1;"/";""))))+1 - finds the position of #
  • MID gets the string after this position.

Btw, to get what is BEFORE last instance of something, use something like this:

  • =LEFT(A1;FIND("#";SUBSTITUTE(A1;"/";"#";LEN(A1)-LEN(SUBSTITUTE(A1;"/";""))))-1)

Leave a Reply

Your email address will not be published.


clear formPost comment