Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 29th 04, 09:55 PM
Steve Mackay
 
Posts: n/a
Default 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





  #2   Report Post  
Old October 29th 04, 10:34 PM
Peo Sjoblom
 
Posts: n/a
Default

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






  #3   Report Post  
Old October 31st 04, 01:19 PM
ivano
 
Posts: n/a
Default


"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


  #4   Report Post  
Old November 1st 04, 06:58 PM
Steve Mackay
 
Posts: n/a
Default

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
working folder David Excel Discussion (Misc queries) 1 January 10th 05 09:52 PM
The available macros list in XL; how to suppress filename from showing KR Excel Discussion (Misc queries) 1 January 10th 05 08:20 PM
How can I change the appearance of the folder icon Margie Excel Discussion (Misc queries) 4 December 29th 04 03:41 PM
How do i save backup copy in a different folder mekraj Excel Discussion (Misc queries) 3 December 17th 04 09:33 PM
cannot save file - folder is read only BigDog Excel Discussion (Misc queries) 1 December 8th 04 12:18 AM


All times are GMT +1. The time now is 12:03 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017