15th September 2016 at 1:46am
Keywords: Excel
Abstract
You need to extract a substring after the last occurrence of a delimiter.. For example you have a full filename including a path and need the basename.
In cell C2:
We have fullname:
/home/reports/client_name.txt
In a different cell:
We want basename: client_name.txt
Function string (assume looking for last / in C2)
=RIGHT(C2,LEN(C2)-FIND("|",SUBSTITUTE(C2,"/","|",LEN(C2)-LEN(SUBSTITUTE(C2,"/","")))))
Explanation of Function String
- FIND number of slashes in string
LEN(C2)-LEN(SUBSTITUTE(C2,"/",""))
- change the last / to a pipe
SUBSTITUTE(C2,"/","|",...)
- get the location of the last / (now a pipe)
FIND("|",...)
- Extract data after the last /
RIGHT(C2,LEN(C2)-...)