Tjyylli

Smooth vector fields on a surface modulo diffeomorphisms

Why restrict private health insurance?

Difference between `nmap local-IP-address` and `nmap localhost`

Why aren't there more Gauls like Obelix?

Rationale to prefer local variables over instance variables?

Use Mercury as quenching liquid for swords?

Converting from "matrix" data into "coordinate" data

Numerical value of Determinant far from what it is supposed to be

Why is it common in European airports not to display the gate for flights until around 45-90 minutes before departure, unlike other places?

How do I increase the number of TTY consoles?

Short scifi story where reproductive organs are converted to produce "materials", pregnant protagonist is "found fit" to be a mother

Too soon for a plot twist?

Locked Away- What am I?

Will expression retain the same definition if particle is changed?

How exactly does an Ethernet collision happen in the cable, since nodes use different circuits for Tx and Rx?

Movie: boy escapes the real world and goes to a fantasy world with big furry trolls

cannot log in to the server after changing SSH port

Can one live in the U.S. and not use a credit card?

Do black holes violate the conservation of mass?

I am the person who abides by rules, but breaks the rules. Who am I?

How to copy the rest of lines of a file to another file

Playing a 7-string guitar song on a 6-string guitar

How can a demon take control of a human body during REM sleep?

Gomel chasadim tovim - are there bad chasadim?

Extract URL from anchor Text in Excel

Hyperlinking a piece of text in ExcelExtract text segments from dataExtract Text from String in ExcelExcel - copy text from url to cellIs it possible to extract url from hyperlinks in excel if it's setup like this?Excel VBA to extract Hyperlink from Hyperlink FormulaMoving Excel document breaks hyperlinks URL formatExtract URL from a cell having =Hyperlink() formula applied to itExtract data from a very messy text file into ExcelExcel: Click on hyperlink calls URL three times

0

I've looked through this thread for something that fits my query but most of the topics came close but wasn't specific to mine.

I'm trying to extract the URL from an anchor text in Excel.

Is there a formula for this? When I try =GETURL or =HYPERLINK it still puts in the anchor text with the URL bound to it.

Any ideas? Macro solutions are pretty useless so a formula would be sweet. Otherwise, lay the macros on me.

microsoft-excel hyperlink links

share|improve this question

edited Dec 20 '13 at 13:15

Jawa

3,15982435

asked Dec 20 '13 at 12:52

user283454user283454

11

bumped to the homepage by Community♦ 14 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Please give an example of your anchor text and how it's anchored. Also, show us the formula you used so we can help you if there was an error in the syntax.
  
  – CharlieRB
  Dec 20 '13 at 14:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Like the VBA here? Create a function in VBA and use that function to pull the address out?
  
  – Raystafarian
  Dec 20 '13 at 14:08
  
  
  

  
  
  
  

add a comment | 

0

I've looked through this thread for something that fits my query but most of the topics came close but wasn't specific to mine.

I'm trying to extract the URL from an anchor text in Excel.

Is there a formula for this? When I try =GETURL or =HYPERLINK it still puts in the anchor text with the URL bound to it.

Any ideas? Macro solutions are pretty useless so a formula would be sweet. Otherwise, lay the macros on me.

microsoft-excel hyperlink links

share|improve this question

edited Dec 20 '13 at 13:15

Jawa

3,15982435

asked Dec 20 '13 at 12:52

user283454user283454

11

bumped to the homepage by Community♦ 14 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Please give an example of your anchor text and how it's anchored. Also, show us the formula you used so we can help you if there was an error in the syntax.
  
  – CharlieRB
  Dec 20 '13 at 14:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Like the VBA here? Create a function in VBA and use that function to pull the address out?
  
  – Raystafarian
  Dec 20 '13 at 14:08
  
  
  

  
  
  
  

add a comment | 

I've looked through this thread for something that fits my query but most of the topics came close but wasn't specific to mine.

I'm trying to extract the URL from an anchor text in Excel.

Is there a formula for this? When I try =GETURL or =HYPERLINK it still puts in the anchor text with the URL bound to it.

Any ideas? Macro solutions are pretty useless so a formula would be sweet. Otherwise, lay the macros on me.

microsoft-excel hyperlink links

share|improve this question

edited Dec 20 '13 at 13:15

Jawa

3,15982435

asked Dec 20 '13 at 12:52

user283454user283454

11

share|improve this question

edited Dec 20 '13 at 13:15

Jawa

3,15982435

asked Dec 20 '13 at 12:52

user283454user283454

11

share|improve this question

edited Dec 20 '13 at 13:15

Jawa

3,15982435

asked Dec 20 '13 at 12:52

user283454user283454

11

edited Dec 20 '13 at 13:15

Jawa

3,15982435

edited Dec 20 '13 at 13:15

Jawa

3,15982435

asked Dec 20 '13 at 12:52

user283454user283454

11

asked Dec 20 '13 at 12:52

user283454user283454

11

1 Answer
1

active

oldest

votes

0

If you have a cell that contains a hyperlink and you wish to see the URL rather than the "friendly name", then consider the following User Defined Function (UDF):

Public Function hyp(r As Range) As String

    hyp = ""

    If r.Hyperlinks.Count > 0 Then

        hyp = r.Hyperlinks(1).Address

        Exit Function

    End If

    If r.HasFormula Then

        rf = r.Formula

        dq = Chr(34)

        If InStr(rf, dq) = 0 Then

        Else

            hyp = Split(r.Formula, dq)(1)

        End If

    End If

End Function

This UDF works with both Inserted Hyperlinks and =HYPERLINK() functions.

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window


2. ALT-I
   ALT-M opens a fresh module


3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.
If you are using a version of Excel later then 2003, you must save
the file as .xlsm rather than .xlsx

To remove the UDF:

1. bring up the VBE window as above


2. clear the code out


3. close the VBE window

To use the UDF from Excel:

=hyp(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

for specifics on UDFs

Macros must be enabled for this to work!

share|improve this answer

answered Dec 22 '13 at 14:54

Gary's StudentGary's Student

13.8k31730

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f691116%2fextract-url-from-anchor-text-in-excel%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

If you have a cell that contains a hyperlink and you wish to see the URL rather than the "friendly name", then consider the following User Defined Function (UDF):

Public Function hyp(r As Range) As String

    hyp = ""

    If r.Hyperlinks.Count > 0 Then

        hyp = r.Hyperlinks(1).Address

        Exit Function

    End If

    If r.HasFormula Then

        rf = r.Formula

        dq = Chr(34)

        If InStr(rf, dq) = 0 Then

        Else

            hyp = Split(r.Formula, dq)(1)

        End If

    End If

End Function

This UDF works with both Inserted Hyperlinks and =HYPERLINK() functions.

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window


2. ALT-I
   ALT-M opens a fresh module


3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.
If you are using a version of Excel later then 2003, you must save
the file as .xlsm rather than .xlsx

To remove the UDF:

1. bring up the VBE window as above


2. clear the code out


3. close the VBE window

To use the UDF from Excel:

=hyp(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

for specifics on UDFs

Macros must be enabled for this to work!

share|improve this answer

answered Dec 22 '13 at 14:54

Gary's StudentGary's Student

13.8k31730

add a comment | 

0

If you have a cell that contains a hyperlink and you wish to see the URL rather than the "friendly name", then consider the following User Defined Function (UDF):

Public Function hyp(r As Range) As String

    hyp = ""

    If r.Hyperlinks.Count > 0 Then

        hyp = r.Hyperlinks(1).Address

        Exit Function

    End If

    If r.HasFormula Then

        rf = r.Formula

        dq = Chr(34)

        If InStr(rf, dq) = 0 Then

        Else

            hyp = Split(r.Formula, dq)(1)

        End If

    End If

End Function

This UDF works with both Inserted Hyperlinks and =HYPERLINK() functions.

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window


2. ALT-I
   ALT-M opens a fresh module


3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.
If you are using a version of Excel later then 2003, you must save
the file as .xlsm rather than .xlsx

To remove the UDF:

1. bring up the VBE window as above


2. clear the code out


3. close the VBE window

To use the UDF from Excel:

=hyp(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

for specifics on UDFs

Macros must be enabled for this to work!

share|improve this answer

answered Dec 22 '13 at 14:54

Gary's StudentGary's Student

13.8k31730

add a comment | 

If you have a cell that contains a hyperlink and you wish to see the URL rather than the "friendly name", then consider the following User Defined Function (UDF):

Public Function hyp(r As Range) As String

    hyp = ""

    If r.Hyperlinks.Count > 0 Then

        hyp = r.Hyperlinks(1).Address

        Exit Function

    End If

    If r.HasFormula Then

        rf = r.Formula

        dq = Chr(34)

        If InStr(rf, dq) = 0 Then

        Else

            hyp = Split(r.Formula, dq)(1)

        End If

    End If

End Function

This UDF works with both Inserted Hyperlinks and =HYPERLINK() functions.

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window


2. ALT-I
   ALT-M opens a fresh module


3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.
If you are using a version of Excel later then 2003, you must save
the file as .xlsm rather than .xlsx

To remove the UDF:

1. bring up the VBE window as above


2. clear the code out


3. close the VBE window

To use the UDF from Excel:

=hyp(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

for specifics on UDFs

Macros must be enabled for this to work!

share|improve this answer

answered Dec 22 '13 at 14:54

Gary's StudentGary's Student

13.8k31730

Public Function hyp(r As Range) As String

    hyp = ""

    If r.Hyperlinks.Count > 0 Then

        hyp = r.Hyperlinks(1).Address

        Exit Function

    End If

    If r.HasFormula Then

        rf = r.Formula

        dq = Chr(34)

        If InStr(rf, dq) = 0 Then

        Else

            hyp = Split(r.Formula, dq)(1)

        End If

    End If

End Function

share|improve this answer

answered Dec 22 '13 at 14:54

Gary's StudentGary's Student

13.8k31730

answered Dec 22 '13 at 14:54

Gary's StudentGary's Student

13.8k31730

answered Dec 22 '13 at 14:54

Gary's StudentGary's Student

13.8k31730