Convert part of string to Hyperlink Text

I'm looking for a way, i would imagine VBA is the only way to make this possible, to convert a long hyperlink to a short hyperlink text based on the last value after the last forward slash(backslash on networks).

I.e.

C:\Users\user\Documents\drawing1.bmp
C:\Users\user\Documents\anime.bmp
C:\Users\user\Documents\photo1.bmp
C:\Users\user\Documents\sketch1.bmp
C:\Users\user\Documents\picture1.bmp

Consider these links, now multiply this by infinity amount of sub-folders and document types. I would like to change these path into single filename hyperlinks

drawing1.bmp
anime.bmp
photo1.bmp
sketch1.bmp
picture1.bmp

I have no idea where to start, i would even appreciate it if the only way is to make the next cell column over the new data. I could then delete the previous column and move that one.

Thanks ahead of time.

This is a start, but that would only work for the Documents folder. I would also need it to read sub-folders. I say this is a start because i could then use

Please Login or Register  to view this content.

to combine it

Edited...
Sorry you did mention the paths will change...

Formula:

Please Login or Register  to view this content.

Assuming the first occurrence is in [A1][/QUOTE]

Wow! and not even VBA based.

Now my only Dilema is that i used

Please Login or Register  to view this content.

but that doesn't let me delete the original cells