Get only file name from file location string.

[Newbie212]

Hello i made a macro that uploads files, using FileToOpen in vba that Browses for your File and Imports Range in a cell with output that looks something like this:

C:\Users\User\Desktop\Example Folder Name\Test.pdf

My question is there a formula or some other way to get only the file name, or in other words - Test.pdf in the above example, displayed in the cell(or some other "display only" cell)? This must not change the value of the cell because i need the whole file location string to upload the file.

Im sorry if its trivial question, thanks in advance.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Like this?

21 10 21.xlsm
	A	B
1	C:\Users\User\Desktop\Example Folder Name\Test.pdf	Test.pdf
FName

Cell Formulas
Range	Formula
B1	B1	=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",100)),100))

 

[Newbie212]

I suggest that you update your Account details

Done.

Thank you very much, Peter. Woks like a charm and as bonus took us like 10 minutes of experimenting and discussing with colleagues what the formula does exactly and how does it work, witch was good fun.

Once again. Thank you and have a nice day.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up. (Also glad to give you and your colleagues some brain exercise. )

Thanks for updating your profile.

 

[Peter_SSs]

BTW, I forgot to mention that you could also do this quickly manually ..

1. Copy/Paste the whole column to a blank column
2. Do a Find/Replace on that new column with these settings: