How can I extract URL from hyperlinks?

%3CLINGO-SUB%20id%3D%22lingo-sub-1286119%22%20slang%3D%22en-US%22%3EHow%20can%20I%20extract%20URL%20from%20hyperlinks%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1286119%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20worksheet%20where%20one%20column%20contains%20hyperlinks%20(like%20created%20with%20%3DHYPERLINK(link%3Btext)).%20How%20can%20I%20create%20a%20formula%20extracting%20link%20urls%20from%20those%20hyperlinks%3F%20I've%20tried%20dozen%20of%20search%20queries%20and%20carefully%20read%20everything%20found%2C%20but%20couldn't%20find%20a%20way.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1286119%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1286324%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20extract%20URL%20from%20hyperlinks%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1286324%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F611636%22%20target%3D%22_blank%22%3E%40tachyglossus%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHello%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3ERight-click%20a%20hyperlink.%3C%2FLI%3E%0A%3CLI%3EFrom%20the%20Context%20menu%2C%20choose%20Edit%20Hyperlink.%20Excel%20displays%20the%20Edit%20Hyperlink%20dialog%20box.%3C%2FLI%3E%0A%3CLI%3ESelect%20and%20copy%20(%3CSTRONG%3ECtrl%2BC%3C%2FSTRONG%3E)%20the%20entire%20URL%20from%20the%20Address%20field%20of%20the%20dialog%20box.%3C%2FLI%3E%0A%3CLI%3EPress%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EEsc%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eto%20close%20the%20Edit%20Hyperlink%20dialog%20box.%3C%2FLI%3E%0A%3CLI%3EPaste%20the%20URL%20into%20any%20cell%20desired.%3C%2FLI%3E%0A%3C%2FOL%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1286339%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20extract%20URL%20from%20hyperlinks%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1286339%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F611636%22%20target%3D%22_blank%22%3E%40tachyglossus%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHello%2C%20you%20can%20follow%20the%20steps%20in%20the%20link%20below%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fhowtouseexcel.net%2Fhow-to-extract-a-url-from-a-hyperlink-on-excel%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fhowtouseexcel.net%2Fhow-to-extract-a-url-from-a-hyperlink-on-excel%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2188188%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20extract%20URL%20from%20hyperlinks%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2188188%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421698%22%20target%3D%22_blank%22%3E%40Abiola1%3C%2FA%3E%26nbsp%3BThe%20link%20you%20provided%20no%20longer%20seems%20to%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2188436%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20extract%20URL%20from%20hyperlinks%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2188436%22%20slang%3D%22en-US%22%3ESee%20if%20this%20works%3A%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fweb.archive.org%2Fweb%2F20210117234752%2Fhttps%3A%2F%2Fhowtouseexcel.net%2Fhow-to-extract-a-url-from-a-hyperlink-on-excel%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fweb.archive.org%2Fweb%2F20210117234752%2Fhttps%3A%2F%2Fhowtouseexcel.net%2Fhow-to-extract-a-url-from-a-hyperlink-on-excel%3C%2FA%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2238633%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20extract%20URL%20from%20hyperlinks%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2238633%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F611636%22%20target%3D%22_blank%22%3E%40tachyglossus%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20another%20non-scripting%20method%20I've%20used%20for%20years.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20Copy%20your%20column%20containing%20the%20links%20and%20paste%20it%20into%20a%20Word%20document.%3C%2FP%3E%3CP%3E2.%20Save%20your%20Word%20document%20as%20%22Web%20Page%20(*.htm%2C%20*.html)%22.%3C%2FP%3E%3CP%3E3.%20Right-click%20on%20the%20.htm%20file%20and%20open%20in%20Notepad%20(to%20view%20the%20html%20source%20code).%3C%2FP%3E%3CP%3E4.%20Find%20the%20rows%20of%20code%20that%20contain%20your%20hyperlinks%20and%20copy%20them%2C%20then%20paste%20into%20a%20Word%20document.%26nbsp%3B%3C%2FP%3E%3CP%3E5.%20Use%20find%20and%20replace%20to%20delete%20unwanted%20code%20until%20you%20are%20left%20with%20only%20the%20hyperlinks.%26nbsp%3B%3C%2FP%3E%3CP%3E6.%20Create%20a%20new%20column%20next%20to%20your%20original%20column%20in%20your%20spreadsheet%20and%20paste%20them%20in.%26nbsp%3B%3C%2FP%3E%3CP%3E7.%20Check%20the%20beginning%20and%20end%20to%20make%20certain%20the%20URLs%20are%20pasted%20back%20into%20the%20corresponding%20rows.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2239350%22%20slang%3D%22de-DE%22%3ERe%3A%20How%20can%20I%20extract%20URL%20from%20hyperlinks%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2239350%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F611636%22%20target%3D%22_blank%22%3E%40tachyglossus%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.extendoffice.com%2Fdocuments%2Fexcel%2F1177-excel-extract-hyperlink-from-cell.html%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EExtract%20actual%20addresses%20from%20hyperlinks%20with%20VBA%20code%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%22%3E%3CSPAN%3EPress%20on%20the%20heading%20to%20get%20more%20possibilities%20and%20options.%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHold%20down%20the%20ALT%20%2B%20F11%20keys%20to%20open%20the%20Microsoft%20Visual%20Basic%20for%20Applications%20window.%3C%2FP%3E%3CP%3EClick%20Insert%26gt%3B%20Module%20and%20paste%20the%20following%20code%20into%20the%20module%20window.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20Extracthyperlinks%20()%3C%2FP%3E%3CP%3E'Updateby%20Extendoffice%3C%2FP%3E%3CP%3EDim%20Rng%20As%20Range%3C%2FP%3E%3CP%3EDim%20WorkRng%20As%20Range%3C%2FP%3E%3CP%3EOn%20Error%20Resume%20Next%3C%2FP%3E%3CP%3ExTitleId%20%3D%20%22KutoolsforExcel%22%3C%2FP%3E%3CP%3ESet%20WorkRng%20%3D%20Application.Selection%3C%2FP%3E%3CP%3ESet%20WorkRng%20%3D%20Application.InputBox%20(%22Range%22%2C%20xTitleId%2C%20WorkRng.Address%2C%20Type%3A%20%3D%20%3CLI-EMOJI%20id%3D%22lia_smiling-face-with-sunglasses%22%20title%3D%22%3Asmiling_face_with_sunglasses%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3EFor%20Each%20Rng%20In%20WorkRng%3C%2FP%3E%3CP%3EIf%20Rng.Hyperlinks.Count%26gt%3B%200%20Then%3C%2FP%3E%3CP%3ERng.Value%20%3D%20Rng.Hyperlinks.Item%20(1)%20.Address%3C%2FP%3E%3CP%3EEnd%20If%3C%2FP%3E%3CP%3ENext%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20press%20F5%20when%20you%20press%20the%20key%20to%20run%20the%20code%2C%20it%20will%20bring%20up%20a%20dialog%20box%20for%20you%20to%20choose%20the%20hyperlinks%20that%20you%20want%20to%20extract%20the%20actual%20addresses%20from.%3C%2FP%3E%3CP%3EThen%20click%20OK%20and%20the%20selected%20cell%20contents%20will%20be%20converted%20to%20the%20real%20hyperlink%20addresses%20in%20the%20original%20range.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2665555%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20extract%20URL%20from%20hyperlinks%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2665555%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3EOpen%20up%20a%20new%20workbook.%3C%2FLI%3E%3CLI%3EGet%20into%20VBA%20(Press%26nbsp%3B%3CSTRONG%3EAlt%2BF11%3C%2FSTRONG%3E)%3C%2FLI%3E%3CLI%3EInsert%20a%20new%20module%20(%3CSTRONG%3EInsert%20%26gt%3B%20Module%3C%2FSTRONG%3E)%3C%2FLI%3E%3CLI%3ECopy%20and%20Paste%20the%20Excel%20user%20defined%20function%20below%3C%2FLI%3E%3CLI%3EPress%26nbsp%3B%3CSTRONG%3EF5%3C%2FSTRONG%3E%26nbsp%3Band%20click%20%E2%80%9CRun%E2%80%9D%3C%2FLI%3E%3CLI%3EGet%20out%20of%20VBA%20(Press%26nbsp%3B%3CSTRONG%3EAlt%2BQ%3C%2FSTRONG%3E)%3C%2FLI%3E%3C%2FOL%3E%3CP%3ESub%20ExtractHL()%3CBR%20%2F%3EDim%20HL%20As%20Hyperlink%3CBR%20%2F%3EFor%20Each%20HL%20In%20ActiveSheet.Hyperlinks%3CBR%20%2F%3EHL.Range.Offset(0%2C%201).Value%20%3D%20HL.Address%3CBR%20%2F%3ENext%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F611636%22%20target%3D%22_blank%22%3E%40tachyglossus%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fmy-notebook-online-2021.blogspot.com%2F2021%2F08%2Fhow-to-extract-hyperlink-in-excel.html%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EFor%20More%20Info%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2703373%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20extract%20URL%20from%20hyperlinks%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2703373%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1132419%22%20target%3D%22_blank%22%3E%40datta9381%3C%2FA%3E%26nbsp%3B%20Thank%20you%2C%20that%20saved%20me%20some%20time.%20I%20just%20had%20to%20add%20the%20SubAddress%20property%20to%20get%20the%20full%20address%20of%20my%20links%20instead%20of%20just%20the%20domain%20piece%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHL.Range.Offset(0%2C%201).value%20%3D%20HL.Address%20%26amp%3B%20HL.SubAddress%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2703437%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20extract%20URL%20from%20hyperlinks%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2703437%22%20slang%3D%22en-US%22%3EIf%20you%20are%20using%20HYPERLINK%20formula%2C%20even%20its%20a%20complex%20formula%20to%20generate%20your%20link%20address%20you%20just%20need%20copy%20the%20same%20formula%20and%20instead%20of%20HYPERLINK(link%3Btext))%20you%20can%20delete%20the%20formula%20where%20will%20remain%20only%20the%20link%3CBR%20%2F%3Efrom%3CBR%20%2F%3E%3DHYPERLINK(A1%3B%22Friendly%20Name%22%3CBR%20%2F%3Eto%3CBR%20%2F%3E%3DA1%3C%2FLINGO-BODY%3E
Occasional Visitor

I have a worksheet where one column contains hyperlinks (like created with =HYPERLINK(link;text)). How can I create a formula extracting link urls from those hyperlinks? I've tried dozen of search queries and carefully read everything found, but couldn't find a way.

9 Replies

@tachyglossus 

Hello,

 

  1. Right-click a hyperlink.
  2. From the Context menu, choose Edit Hyperlink. Excel displays the Edit Hyperlink dialog box.
  3. Select and copy (Ctrl+C) the entire URL from the Address field of the dialog box.
  4. Press Esc to close the Edit Hyperlink dialog box.
  5. Paste the URL into any cell desired.

@Abiola1 The link you provided no longer seems to work.

@tachyglossus

 

Here's another non-scripting method I've used for years.

 

1. Copy your column containing the links and paste it into a Word document.

2. Save your Word document as "Web Page (*.htm, *.html)".

3. Right-click on the .htm file and open in Notepad (to view the html source code).

4. Find the rows of code that contain your hyperlinks and copy them, then paste into a Word document. 

5. Use find and replace to delete unwanted code until you are left with only the hyperlinks. 

6. Create a new column next to your original column in your spreadsheet and paste them in. 

7. Check the beginning and end to make certain the URLs are pasted back into the corresponding rows.

@tachyglossus 

 

Extract actual addresses from hyperlinks with VBA code

Press on the heading to get more possibilities and options.

 

Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

  Click Insert> Module and paste the following code into the module window.

 

Sub Extracthyperlinks ()

'Updateby Extendoffice

Dim Rng As Range

Dim WorkRng As Range

On Error Resume Next

xTitleId = "KutoolsforExcel"

Set WorkRng = Application.Selection

Set WorkRng = Application.InputBox ("Range", xTitleId, WorkRng.Address, Type: =

For Each Rng In WorkRng

     If Rng.Hyperlinks.Count> 0 Then

         Rng.Value = Rng.Hyperlinks.Item (1) .Address

     End If

Next

End Sub

 

Then press F5 when you press the key to run the code, it will bring up a dialog box for you to choose the hyperlinks that you want to extract the actual addresses from.

Then click OK and the selected cell contents will be converted to the real hyperlink addresses in the original range.

 

 

  1. Open up a new workbook.
  2. Get into VBA (Press Alt+F11)
  3. Insert a new module (Insert > Module)
  4. Copy and Paste the Excel user defined function below
  5. Press F5 and click “Run”
  6. Get out of VBA (Press Alt+Q)

Sub ExtractHL()
Dim HL As Hyperlink
For Each HL In ActiveSheet.Hyperlinks
HL.Range.Offset(0, 1).Value = HL.Address
Next
End Sub

@tachyglossus 

For More Info

@datta9381  Thank you, that saved me some time. I just had to add the SubAddress property to get the full address of my links instead of just the domain piece:

 

HL.Range.Offset(0, 1).value = HL.Address & HL.SubAddress

 

If you are using HYPERLINK formula, even its a complex formula to generate your link address you just need copy the same formula and instead of HYPERLINK(link;text)) you can delete the formula where will remain only the link
from
=HYPERLINK(A1;"Friendly Name"
to
=A1