How to extract text from a string before a last specified character
Category: [Excel] Demo Available
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 :
Code:
=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:
| How to extract text string |
| How to extract letters from the string |
| How to create a custom function to extract integers from a simple 11 character string |
| How to add digit or replace last digit in string based on criteria |
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.