Extract text after the last instance of some character

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)

Why?

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