I have a formula linking to a separate file. I need to extract the file name as text. For example: formula says
=+'W:\test budget\[East.xlsx]2015 Budget'!$B$1
In the cell below I need 'East.xlsx
=REPLACE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),1,
FIND("[",CELL("filename",A1)),"")
Function Fname(r As Range) As String
Fname = Split(Replace(r.Formula, "[", "]"), "]")(1)
End Function
Try...
Rich (BB code):=REPLACE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),1, FIND("[",CELL("filename",A1)),"")
Cell Formulas | ||
---|---|---|
Range | Formula | |
A1 | =+'W:\test budget\[East.xlsx]2015 Budget'!$B$1 | |
C1 | =MID(B1,FIND("[",B1)+1,FIND("]",B1)-FIND("[",B1)-1) |
This works, thank you. Is there any way to extract East.xlsx directly from cell A1 rather than as a 2 step process(e.g. avoiding cell B2)?
=REPLACE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),1,
FIND("[",CELL("filename",A1)),"")