extract file name from path in Excel
Q31. In Excel, how can I extract file name from a full path?
To do it in Excel, here is the answer:
a) Enter the formula =TRIM(RIGHT(SUBSTITUTE(A2,"\",REPT(" ",100)),100)) as shown below where A2 corresponds to the cell containing the full path with file name.
Basically this formula replaces all "\" characters with 100 blank spaces. Then the right most 100 characters is extracted and trimmed to get the file name. 100 spaces is used assuming file name length will be less than 100 characters.
Technically the file name could be 255 characters long in which case 100 in the formula should be replaced by 255.