asked on

Extract Text from URL String

I have a list of URL's that I copied that have the Text and the URL. I know how to get rid of the URL and just have Text but I want the opposite. For instance, if my copied URL is:

Google <----- This is the link in Excel. Under it is https://www.google.com

I want to extract the URL from the Text so the result is:

http://www.google.com

Is there a formula to do this?

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Subodh Tiwari (Neeraj)

You may simply place this User Defined Function on a Standard Module like Module1...

Function getURL(Rng As Range) As String
If Rng.Hyperlinks.Count > 0 Then getURL = Rng.Hyperlinks(1).Address
End Function

Open in new window

Assuming your hyperlink is in A2 then you may use this function on the Worksheet like a Regular Function...

=getURL(A2)

Open in new window

To implement this UDF to your file, follow these steps...

1) Open your file and press Alt+F11 to open the VB Editor.
2) On VB Editor's Ribbon --> Insert --> Module -> Copy the code given above and paste it into the opened code window.
3) Close the VB Editor and save your file as Macro-Enabled Workbook.

Software Squirrel

ASKER

Both solutions are correct. Ryan added an Excel Workbook Macro-Enabled as an example. That was helpful because even though the steps seem intuitive, I could not get it to work. With the Workbook, I got it to work.