Google Apps Script Read if Cell Has Hyperlink

iii Ways to Excerpt the URL from a Hyperlink in Google Sheets

This post will show y'all how you tin can extract a URL from a hyperlink in Google Sheets.

There are a few reasons why you might want to extract a URL from a hyperlink.

Information technology could be that you lot may desire to use the URL in another sheet or use it in an entirely different application.

Some other situation where you lot might discover yourself needing to extract a URL from a hyperlink will occur when you re-create and paste data from the spider web.

When this happens, only the anchor text appears in your spreadsheet. You will need to do some extra piece of work if you desire to see the URL.

In this post, you lot will meet 3 methods you can use to get the URL from a hyperlink in Google Sheets. Get your copy of the case workbook to follow along!

Extract the URL from a Hyperlink by Hover and Copy

Bank check out this mail if y'all're unsure what a hyperlink is or how to insert a hyperlink in Google Sheets. Various methods of inserting hyperlinks were explored.

This is a list of hyperlinks to other articles on this website that were copied and pasted directly into the spreadsheet. You lot can extract their URLs using the hover and re-create method.

A quick view box containing the URL of the hyperlink shows up every fourth dimension y'all move your mouse over a jail cell with a hyperlink.

Inside the quick view box, to the right of the URL, in that location are three other icons, each i performing separate tasks.

By hovering your mouse across them, yous will see the function each ane performs.

To extract the URL from the hyperlink, click on the Copy link icon.

Afterward clicking on the Copy link icon, the URL of the hyperlink is copied to your clipboard.

This allows yous to paste the copied URL into any jail cell y'all wish. You lot can use the keyboard shortcut, Ctrl + V, or right-click and choose Paste from the option in the menu.

In those simple and easy steps, you have extracted the URL from a hyperlink.

Excerpt the URL from a Hyperlink by Edit and Re-create

In that location are three ways past which you can access the Edit command for a hyperlink.

The get-go is from the quick view box. Click on the Edit link icon.

You lot can right-click on the cell containing the hyperlink and select the Edit link choice from the card.

You tin can also access the Edit link option from the Insert menu. Besides, the keyboard shortcut Ctrl + K can exist used to edit the hyperlink.

Using any one of these Edit link options volition open the dialogue box you go when you want to add a hyperlink to a cell.

Still, because the cell already contains a hyperlink, the Edit link command opens the dialogue box and automatically highlights the existing URL.

From this point, y'all can either change the URL by inserting a new ane or extract the URL by but copying the highlighted link using the keyboard shortcut Ctrl + C, or by right-clicking on the highlighted link and selecting the copy option.

After you've washed this, click on the cell where you want to insert the URL and paste the copied link using any of the pasting methods identified earlier.

While using either 1 of these methods to excerpt URLs from hyperlinks is uncomplicated and easy to utilise, unfortunately, they have some limitations.

• These methods don't let for the extraction of URLs when the hyperlinks are contained in a single cell.

Hovering your mouse over the cell wouldn't bring the quick view box.

And using any of the Edit link options wouldn't reveal the URL for you to extract since there are multiple URLs in the cell.

To use these copy and paste methods to excerpt URLs, the hyperlinks must be contained in separate cells.

• These methods are not dynamic. They cannot be efficient for use on big sets of hyperlinked. Imagine you lot take hundreds of cells with hyperlinks whose URL you need to extract. It would be dull and consume a lot of time to extract each URL. For large scale URL extraction operations, you may favour a more than technical method such as using an Apps Scripts to excerpt the URL from a hyperlink.

Excerpt URL from a Hyperlink with a Custom Apps Scripts Function

There are hundreds of built-in functions in Google sheets that let you to perform nigh all kinds of operations on your data.

These functions are very efficient at producing results in a fraction of the time it will have taken y'all to manually perform each operation.

Unfortunately, in that location is no built-in function for extracting a URL from a hyperlink.

Apps Script is a feature provided by Google to enable users to create custom functions. Information technology is based on JavaScript, and information technology doesn't crave any software installation or special setup on your computer.

Merely a Google account and a web browser are required to access the script editor.

Cheque out the full guide to getting started with Apps Scripts for more details.

You can access the Script editor from the spreadsheet by going to the Extensions menu option and clicking on Apps Script.

This will open a new window in your browser that displays the google App Script editor.

In the Script editor window, you can change the name of your project from the default Untitled project by double-clicking on it.

The program controls surface area contains commands that permit y'all to perform regular editing activities on your code. You'd generally perform the save (looks similar a diskette icon), run, undo, redo, and debug operations on your code.

The files expanse is where your newly created codes are saved.

With Apps Script, you tin can create a customized dynamic function that will help you to extract URLs from hyperlinks.

Copy the code below into the code window to create and utilize the GetURL role.

          office GetURL(input) {   var myFormula = SpreadsheetApp.getActiveRange().getFormula();   var myAddress = myFormula.supercede('=GetURL(','').replace(')','');   var myRange = SpreadsheetApp.getActiveSheet().getRange(myAddress);   return myRange.getRichTextValue().getLinkUrl()  }        

Afterwards copying and pasting the script, ensure you relieve information technology then that you tin can use it on your spreadsheet.

When you the code on Apps Script for the first fourth dimension, you will be prompted to authorize the code.

Click on Permit when prompted. When y'all're done with this process, your custom Apps Scripts function will be available for employ on your spreadsheet.

          =GetURL(A1)        

Y'all tin can now utilise the above custom office to extract the URL from a hyperlink in cell A1.

Conclusions

While there is no born function to extract URLs from hyperlinks, there are a few workarounds.

You tin copy URLs from the quick view box or the Edit command and paste them into a jail cell, simply this tin can exist tiresome for large datasets.

The most efficient option would be to use a custom function. You lot tin can create a custom Apps Scripts part you lot can then employ in your spreadsheet to extract the URL from any hyperlink.

Have you used any of these options? Are there any other methods you know of? Let me know in the comments beneath!

Oluwaseun is a Business concern Intelligence analyst with proficiency in Excel, Ability BI, and SQL programming language.

langloisjusnis.blogspot.com

Source: https://www.oksheets.com/extract-hyperlink-url/

Share this post