filename with folder
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 |
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),FI ND("[",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) "Steve Mackay" wrote in message ... 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 |
"Steve Mackay" ha scritto nel messaggio ... 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 |
Thank you. Both of these functions work.
Steve Mackay "ivano" wrote in message ... 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 |
All times are GMT +1. The time now is 01:08 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com