Extract file name from formula

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Oct 23, 2014

• #2

Kevinax said:

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

Click to expand...

Try...

Rich (BB code):

=REPLACE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),1,
  FIND("[",CELL("filename",A1)),"")

 

Upvote 0

Momentman

Well-known Member

Joined

Jan 11, 2012

Messages

4,153

Office Version

1. 365

Platform

1. Windows
2. MacOS

• Oct 23, 2014

• #3

Sorry

 

Upvote 0

Peter_SSs

MrExcel MVP, Moderator

Joined

May 28, 2005

Messages

63,880

Office Version

1. 365

Platform

1. Windows

• Oct 23, 2014

• #4

You could try this user-defined function. To implement ..

1. Right click the sheet name tab and choose "View Code".

2. In the Visual Basic window use the menu to Insert|Module

3. Copy and Paste the code below into the main right hand pane that opens at step 2.

4. Close the Visual Basic window.

5. Enter the formula as shown in the screen shot below and copy down.

6. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Code:

Function Fname(r As Range) As String
  Fname = Split(Replace(r.Formula, "[", "]"), "]")(1)
End Function

Excel Workbook
	C
1	#REF!
2	East.xlsx
Extract file name

 

Upvote 0

K

Kevinax

New Member

Joined

Mar 29, 2010

Messages

25

• Oct 23, 2014

• #5

Thanks for the reply but I'm entirely uncomfortable with visual basic.

 

Last edited: Oct 23, 2014

Upvote 0

K

Kevinax

New Member

Joined

Mar 29, 2010

Messages

25

• Oct 23, 2014

• #6

Aladin Akyurek said:

Try...

Rich (BB code):

=REPLACE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),1,
  FIND("[",CELL("filename",A1)),"")

Click to expand...

Thanks for the reply but the formula seems to be giving me the file name of the current file and not the file name referenced in the formula.

 

Upvote 0

K

Kevinax

New Member

Joined

Mar 29, 2010

Messages

25

• Oct 23, 2014

• #7

For added clarification ....... I'm working in a file named Summary.xlsm, this file has the following formula: =+'W:\test budget\[East.xlsx]2015 Budget'!$B$1 I need a formula in the Summary file that returns East.xslm not Summary.xlsm

 

Upvote 0

Momentman

Well-known Member

Joined

Jan 11, 2012

Messages

4,153

Office Version

1. 365

Platform

1. Windows
2. MacOS

• Oct 23, 2014

• #8

I copied the formula as text to B1 and used the MID function in C1. In Excel 2013, you can use the FPRMULATEXT function for cell B1

Excel 2010
	A	B	C
1	#REF!	+'W:\test budget\[East.xlsx]2015 Budget'!$B$1	East.xlsx
Sheet3

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)

 

Upvote 0

K

Kevinax

New Member

Joined

Mar 29, 2010

Messages

25

• Oct 23, 2014

• #9

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)?

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Oct 23, 2014

• #10

Kevinax said:

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)?

Click to expand...

Why don't you enter in A1

Rich (BB code):

=REPLACE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),1,
  FIND("[",CELL("filename",A1)),"")

as suggested?

 

Upvote 0