OzGrid

How to extract text from a string before a last specified character

To extract text from a string before a last specified character up until the same character is encountered again...

e.g.\\ppfil1g\region$\222 Pitt St\CommIT\OPT Tableau Upload\4. Benefits Realisation Framework\Benefits Realisation FY18\PGC31,32,58_Worksmart\Presentation\latest\61D24353.tmp
I need to be able to extract the highlighted string "latest"

To be more specific, to extract the text that appears between last 2 specific characters.

In the e.g. the specific characters are the backslashes and the text between the last 2 backslashes is "latest".

Solution:  With your string located in cell A1 ...

Use the following formula :

=MID(A1,IFERROR(FIND(CHAR(1),SUBSTITUTE(A1,CHAR(92),CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(92),""))-1)),"")+1,FIND("@",SUBSTITUTE(A1,CHAR(92),"@",(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(92),"")))/LEN(CHAR(92))))-IFERROR(FIND(CHAR(1),SUBSTITUTE(A1,CHAR(92),CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(92),""))-1)),"")-1)

Obtained from the OzGrid Help Forum.

Solution posted by Carim

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

See also:

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery