Remove path from file name

[dantb]

Hello all: Is there a formula where only the data after the second or third \ be shown in a cell no mater what the path is? Thanks Dan

D:\A Print\D72PI010-R00.PDF
D:\A Print\Temp\D72PI010-R00.PDF

Formula in both cases would return just the file name D72PI010-R00.PDF

 

[BrianB]

Needs a custom function. Alt +F11 then Copy paste this to a code module.
Usage the same as other functions
=GetFileName(A1)

Function GetFileName(nm As String)
    L = Len(nm)
    C = L
    While Mid(nm, C, 1) <> "\"
        C = C - 1
    Wend
    GetFileName = Right(nm, L - C)
End Function

 

[dantb]

Works great Brian! Played with Len & Right but could never put nothing together that worked. Thanks again for your help. Dan

 

[bandit_1981]

dim Files
dim FileName as string

Files = split(path,"\")
FileName = Files(ubound(files))

This way doesnt loop around but is not better then the abouve just another approach.

 

[dantb]

Thanks Bandit: Will remember both ways. Thank both of you for all your help. Dan

 

[fairwinds]

=REPLACE(A1,1,FIND("@",SUBSTITUTE(A1,"\","@",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))),"")

 

[dantb]

Thanks Fairwinds: Works well also, Im not sure what the (@) symble is doing but the formula does work, Thanks Dan

 

[just_jon]

The @ sign is replacing the last instance of / in the full file name, afterwhich it is used as the search criteria to the FIND function.

 

[dantb]

Thanks Jon: I never have used the ( @ ) symble like that before, will have to play with that one. Thanks again Dan

 

[just_jon]

Thanks Jon: I never have used the ( @ ) symble like that before, will have to play with that one. Thanks again Dan

The @ symbol could have been any unlikely-to-be-found character -- the intent here is to SUBSTITUTE the right-most slash with a unique character on which to key a search.