How to Extract URLs from Hyperlinked Text in Google Sheets Using Apps Script

When you copy and paste data into Google Sheets from a website, the text often retains its hyperlinks. While clicking these links will open the destination URL, extracting the actual URL string into a separate cell is surprisingly difficult using standard spreadsheet formulas. To automate this extraction, you can write a brief custom function using Google Apps Script.

How to Create the Extraction Script

Google Apps Script is a cloud-based JavaScript platform that lets you extend the functionality of Google Workspace applications, including Sheets.

1. Open your Google Sheet containing the hyperlinked text.
2. In the top menu, click on Extensions and select Apps Script. This will open a new browser tab with a blank script editor.
3. Delete any existing code in the editor (usually an empty myFunction block).
4. Copy and paste the following code into the editor:

   function EXTRACT_URL(input) {
     var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
     var range = sheet.getActiveRange();
     var richText = range.getRichTextValue();
     if (richText) {
       return richText.getLinkUrl();
     }
     return "No Link Found";
   }

5. Click the Save project icon (floppy disk) in the toolbar.
6. You can now close the Apps Script browser tab and return to your Google Sheet.

How to Use Your Custom Formula

Now that the script is saved, you can use =EXTRACT_URL() exactly as you would any native Google Sheets function, though you must reference the active cell differently due to how Apps Script reads rich text formatting.

1. Click on an empty cell next to your hyperlinked text.
2. Type =EXTRACT_URL(A1) (assuming your hyperlink is in cell A1) and press Enter.

The cell will briefly display a Loading… message while the Apps Script executes, and then the raw URL string will appear. You can drag the fill handle down to apply this custom function to an entire column of hyperlinked text.