Extracting Pathname / Extension

[DinoZ]

Currently I have a dump of my server in excel. A column currently displays the full file and pathname.

eg.

D:\System\Files\Sys32.xls
C:\Data\Test\Testing.doc

I want three columns next to this that display just the path, just the filename, and just the extension.

Eg.
Pathname: C:\Data\Test\
Filename: Testing.doc
Extension: .doc
etc..

What cell formulas can I use to extract this?
I already have the filename formula and this seems to work for me:
=MID(B2,FIND("*",SUBSTITUTE(B2,"\","*",LEN(B2)-LEN(SUBSTITUTE(B2,"\",""))))+1,LEN(B2))

[Paul]

Since you already have the filename, you can use that to get the pathname, rather than use some long formula again.

If your dump data (full file & path name) is in column B (from B2 downward), use the following formulas:

C2 (path):

Please Login or Register  to view this content.

D2 (filename, your current formula):

Please Login or Register  to view this content.

E2 (extension):

Please Login or Register  to view this content.

For the extension (E2), the formula I provided will handle up to 4-character extensions. If all files had 3-digit extensions you could simply use

=RIGHT(B2,4)

(which will also return the "." before the extension as you requested).

[mudraker]

If your full file name is in c2 then use

=MID(C2,FIND("*",SUBSTITUTE(C2,".","*",LEN(C2)-LEN(SUBSTITUTE(C2,".",""))))+1,LEN(C2))

or
if the file name has only one . in it then you could also use

=MID(B2,FIND("*",SUBSTITUTE(B2,".","*",LEN(B2)-LEN(SUBSTITUTE(B2,".",""))))+1,LEN(B2))

or
if extention are all 3 letters then you could use
=RIGHT(B2,3)