extract url from HYPERLINK function

**Alicewonderland0** · 06-23-2011, 01:41 PM

Hi all,

I have a spreadsheet w/a large column of conference names, with the url for the conference embedded using the HYPERLINK function (eg =HYPERLINK("http://www.meeaconference.org/conference.php","MEEA Annual Midwest Energy Solutions Conference"). I want to have a second column that shows JUST the extracted url - is there a way to do this? It seems all the typical url extraction codes only work for a normal embedded url, not the hyperlink function...

Thank you!!

Alice

**mikerickson** · 06-23-2011, 02:12 PM

Since the url is hard coded into the HYPERLINK function, it would be easier to have the url's in their own cells and the HYPERLINK refer to that cell.

To extract the hyperlink from an existing formula, you can do it through Names.
Assuming that your column of HYPERLINK formulas is column A. Select the cell B1 and define the name
Name: AFormula RefersTo: =GET.CELL(6, Sheet1!$A1)

(Note the relative reference in the name, thus the selection of B1 is important)

Then, putting the formula =TRIM(MID(SUBSTITUTE(AFormula,CHAR(34),REPT(" ",255)),255,255)) in a cell will return link of column A's formula, as in the attached.

**NBVC** · 06-23-2011, 02:29 PM

Here is a UDF:

http://www.ozgrid.com/VBA/HyperlinkAddress.htm

**Kenneth Hobson** · 06-23-2011, 04:56 PM

Here is a UDF that I worked up.

Please Login or Register  to view this content.

**Haseeb Avarakkan** · 06-23-2011, 06:36 PM

Or use Find/Replace command, if the URL is hard coded in the formula.

Copy the range contains HYPERLINK formula to a new range, then select the range. Press Control+H

Find What: =*"
Replace All with blank.

Again,

Find What: ","*

Replace All with blank.

**Jalba sung** · 08-28-2014, 08:56 AM

Hi Can anyone explain the method using Names?
How you do it step by step?
I don't understand what "=GET.CELL()" is in relation to AFORMULA

I tried other VBA methods and none of them worked for me. They all end up showing #Names
But the above Names method worked but I can't do it on my spreadsheet
Thank you.