extract file name from path

**d3ad** · 04-04-2011, 05:55 AM

Hi i have a file location in say cell a1 ie. "C:/files/image.jpeg" is it possible to format this or create a formula in a2 to contain just "image.jpeg"

**pike** · 04-04-2011, 06:58 AM

Hi d3ad
Welcome to the forum

=mid(a1,find("*",substitue(a1,"\","*",len(a1)-len(substitute(a1,"\",))))=1,len(a1))

Can you please change your thread title to extract file name form path

**d3ad** · 04-05-2011, 02:33 AM

Hi Pike,
Thanks for the help but i just get the name? error i have tried to reverse engineer the mid and find formula but to no avail. i am running excel on osx but have checked and all the functions (mid, find, substitute are usable on this version) do you have any clues? thanks again for the help!!!!

**pike** · 04-05-2011, 03:21 AM

Hi d3ad

I have cahnge the \ to / as in your example
but it should be \

so C:/files/image.jpeg becomes image.jpeg

=MID(A1,FIND("*",SUBSTITUTE(A1,"/","*",LEN(A1)-LEN(SUBSTITUTE(A1,"/",))))+1,LEN(A1))

plus removed a spelling mistake in substitute

**martindwilson** · 04-05-2011, 04:59 AM

=trim(right(substitute(a1,"/",rept(" ",25)),25))

**d3ad** · 04-05-2011, 05:21 AM

fantastic thank you guys VERY much! sorry for my bad spelling in the first place!!!

**pike** · 04-05-2011, 05:27 PM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save