Copy the address of an external link as text into another cell

[Syerram]

Hi,


I am trying to copy the address of an external link (not a hyperlink, an external link to a different workbook) as a text string into another cell.


Example: The filename I am working on is called 'Try'. Say cell Sheet1!A1 has this formula: ='[Book2.xls]Sheet1'!A1 which has a value of 5


I want a formula in B1 which will return '[Book2.xls]Sheet1'!A1


So in sense, column A will have all the external links and column B should return the external links in text format (everything the formula in A1 has after "=" sign)


Is there any way of doing this?


Thanks a ton for the help!


Yerram

 

[Luke M]

You'd need to use some sort of VB. Here's a UDF I wrote awhile ago that might help you out.

[pre]

Function GetFormula(r As Range, Optional x As Boolean = False) As String
If r.Count > 1 Then GetFormula = "#VALUE!"
'Check if formula returned in R1C1 style
If x Then
GetFormula = r.FormulaR1C1
Else
GetFormula = r.Formula
End If
End Function

[/pre]
For your case, overall formula would be:

=MID(GetFormula(A1),2,999)

The MID function is there to get rid of the "=" sign at beginning of formula.

 

[Syerram]

works like a charm! thanks so much


do you think there is anyway to do this without UDF?


Thanks a ton!

 

[Luke M]

You could write a macro which essentially does the same thing, but you do have to use VB as there is nothing within native XL that can extract the formula within a cell.

 

[Sajan]

Hi Yerram,

If all you are interested in is seeing the formula text, you could also use the "Show Formulas" menu item. Then you can manually copy the formula text to some other column.


It is available from the "Formulas" ribbon. (That is the case in Excel 2010. Not sure if it is different in other Excel versions.)


Cheers,

Sajan.

 

[Hui]

Yerram


Firstly, Welcome to the Chandoo.org forums.


If your Link is in a cell you can use a Named Formula like

ExtractLink: =GET.FORMULA($A$2)

Then in another cell just use =ExtractLink


=Get.Formula() is an Excel 4 Macro command and can only be used in a Named Formula

 

[Luke M]

Cool trick Hui. Does it only give R1C1 references, or is there a way to get normal references using that trick?

 

[Syerram]

Thanks all for the responses! I used Luke M UDF and sorted it out.


Hui, thanks for the intro. I am not very sure I understood your suggestion - Is Get.Formula an inbuilt macro command in excel? and is that the case for ExtractLink as well?


Cheers!