Monday, May 30, 2005

Using the CELL function in Excel

Finding the full filename and path for any given file can be done fairly simply using the CELL function.
Using =CELL("filename",C5) will return the full path, filename and sheet name.

Extracting the specific information of the file name and path is a little more difficult but can be achieved using the text string functions LEFT, MID, FIND and MID
To get the full path and file name use =LEFT(CELL("filename",C5),FIND("]",CELL("filename",C5)))

To get the file name only use =MID(CELL("filename",C5),FIND("[",CELL("filename",C5))+1,FIND("]",CELL("filename",C5))-FIND("[",CELL("filename",C5))-1)

To get the path easily use the combination of the above two cells listed as cells C20 and C21 respectively =LEFT(C20,LEN(C20)-LEN(C21)-2)


Posted by: Jethro | 11:58 PM |