filename with folder

[Steve Mackay]

Hi,

I need a formula that will return a file name with only the folder it is in,
not the entire file path.

For instance, if the full path of the file is:
C:\Documents and Settings\Steve\Desktop\Africa\[MyFile.xls]

I would want the formula to return:
Africa\[MyFile.xls]

I've have a formula that will return just the file name:
=MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)

And on that returns just the path:
=MID(CELL("filename"),1,FIND("[",CELL("filename"))-1)

But I can't figure out how to combine these two to get a partial path.

Thanks
Steve Mackay

 

[Peo Sjoblom]

First of all you should add a cell reference, use CELL("filename",A1)
else it will return the last active sheet name
To get folder and file name try

=RIGHT(LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-2),LEN(LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-2))-SEARCH("^^",SUBSTITUTE(LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-2),"\","^^",LEN(LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-2))-LEN(SUBSTITUTE(LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-2),"\","")))))&"\"&MID(CELL("filename",A1),FIND("[",CELL("filename",A1)),FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))+1)


If it is always the same path (if it is always a folder on your desktop it
can be simplified)

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[ivano]

Hi,

I need a formula that will return a file name with only the folder it is in,
not the entire file path.

For instance, if the full path of the file is:
C:\Documents and Settings\Steve\Desktop\Africa\[MyFile.xls]

I would want the formula to return:
Africa\[MyFile.xls]

I've have a formula that will return just the file name:
=MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename")
)-FIND("[",CELL("filename"))-1)

And on that returns just the path:
=MID(CELL("filename"),1,FIND("[",CELL("filename"))-1)

But I can't figure out how to combine these two to get a partial path.

Thanks
Steve Mackay

Hy Steve,
try to a array formula (CTRL+SHIFT+ENTER):
=MID(CELL("filename"),LARGE(IF(MID(CELL("filename"),seq,1)="\",seq,""),2)+1,
SEARCH("]",CELL("filename"))-LARGE(IF(MID(CELL("filename"),seq,1)="\",seq,""
),2))

After you have defined "seq" as:
=ROW(INDIRECT("1:1024"))

ivano

 

[Steve Mackay]

Thank you. Both of these functions work.
Steve Mackay