How to extract url from hyperlink in Google sheets?

Extracting URLs from Hyperlinks in Google Sheets

Introduction

Google Sheets is a powerful tool for data analysis and manipulation. However, extracting URLs from hyperlinks can be a challenging task, especially when dealing with complex data. In this article, we will explore the steps to extract URLs from hyperlinks in Google Sheets.

Step 1: Select the Hyperlink

To extract URLs from hyperlinks, you need to select the hyperlink first. You can do this by:

  • Clicking on the hyperlink
  • Right-clicking on the hyperlink and selecting "Copy" (or press Ctrl+C on Windows or Command+C on Mac)
  • Using the keyboard shortcut Ctrl+V (Windows) or Command+V (Mac) to paste the URL

Step 2: Use the "Find and Replace" Function

To extract URLs from hyperlinks, you can use the "Find and Replace" function in Google Sheets. Here’s how:

  • Select the cell where you want to display the extracted URLs
  • Go to the "Data" menu and select "Find and Replace"
  • In the "Find what" field, enter the following formula: =REGEXEXTRACT(A1,"https?://[^[:space:]]+")
  • Replace "A1" with the cell where you want to display the extracted URLs
  • Click "Find" to find all occurrences of the URL
  • Click "Replace all" to replace the URLs with the extracted values

Step 3: Use the "Filter" Function

To extract URLs from hyperlinks, you can use the "Filter" function in Google Sheets. Here’s how:

  • Select the cell where you want to display the extracted URLs
  • Go to the "Data" menu and select "Filter"
  • In the "Filter" field, enter the following formula: =FILTER(A1:A10,"https?://[^[:space:]]+")
  • Replace "A1:A10" with the range of cells where you want to extract the URLs
  • Click "Filter" to filter the data and extract the URLs

Step 4: Use the "Text to Columns" Function

To extract URLs from hyperlinks, you can use the "Text to Columns" function in Google Sheets. Here’s how:

  • Select the cell where you want to display the extracted URLs
  • Go to the "Data" menu and select "Text to Columns"
  • In the "Text to Columns" field, enter the following formula: =TEXT(A1,"https?://[^[:space:]]+")
  • Click "Convert" to convert the text to columns
  • Click "Finish" to apply the columns

Step 5: Use the "Filter" Function (again)

To extract URLs from hyperlinks, you can use the "Filter" function again. Here’s how:

  • Select the cell where you want to display the extracted URLs
  • Go to the "Data" menu and select "Filter"
  • In the "Filter" field, enter the following formula: =FILTER(A1:A10,"https?://[^[:space:]]+")
  • Replace "A1:A10" with the range of cells where you want to extract the URLs
  • Click "Filter" to filter the data and extract the URLs

Tips and Variations

  • To extract URLs from hyperlinks in multiple columns, you can use the "Text to Columns" function and then use the "Filter" function to extract the URLs.
  • To extract URLs from hyperlinks in a specific range, you can use the "Filter" function and then use the "Text to Columns" function to extract the URLs.
  • To extract URLs from hyperlinks in a specific column, you can use the "Filter" function and then use the "Text to Columns" function to extract the URLs.

Example Use Case

Suppose you have a table with the following data:

Hyperlink URL
https://www.example.com https://www.example.com/about
https://www.google.com https://www.google.com/search
https://www.facebook.com https://www.facebook.com/about

To extract the URLs from the hyperlinks, you can use the following steps:

  1. Select the cell where you want to display the extracted URLs
  2. Go to the "Data" menu and select "Find and Replace"
  3. In the "Find what" field, enter the following formula: =REGEXEXTRACT(A1,"https?://[^[:space:]]+")
  4. Replace "A1" with the cell where you want to display the extracted URLs
  5. Click "Find" to find all occurrences of the URL
  6. Click "Replace all" to replace the URLs with the extracted values

The resulting table will look like this:

Hyperlink URL
https://www.example.com https://www.example.com/about
https://www.google.com https://www.google.com/search
https://www.facebook.com https://www.facebook.com/about

Conclusion

Extracting URLs from hyperlinks in Google Sheets can be a challenging task, but with the right steps and formulas, you can achieve the desired result. By following the steps outlined in this article, you can extract URLs from hyperlinks and display them in your Google Sheets. Remember to use the "Find and Replace" function, "Filter" function, and "Text to Columns" function to extract the URLs from hyperlinks.

Unlock the Future: Watch Our Essential Tech Videos!


Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top