Seemanto Saha
We often have to copy and paste data from several sources. Sometimes, these copied data contain hyperlinks to the page from where they are copied. There are several ways to remove these hyperlinks. In this article, I’ll demonstrate 5 simple ways to remove hyperlink in Google Sheets.
A Sample of Practice Spreadsheet
You can copy our practice spreadsheets by clicking on the following link. The spreadsheets contain an overview of the datasheet and an outline of the described ways to remove hyperlink in Google Sheets.
5 Simple Ways to Remove Hyperlink in Google Sheets
First, let’s get familiar with our dataset. The dataset contains the name of several Academy Award Best Picture Winner films and the year they were released.
If you hover your mouse pointer above any cell in range C5:C11, you will see that the values are hyperlinked to Wikipedia pages. We’ll remove these hyperlinks.
1. Using Remove Link Icon
The simplest way to remove any link from a cell is to use the Remove link icon from the appeared link.
Steps:
- First, select Cell C5 and hover your mouse pointer above the cell. A window for the linked site will appear. From there, click on the Remove Link icon.
- The hyperlink for Cell C5 has now been removed.
- Manually, continue the same for other cells of Column C.
2. Removing with Right Click of Mouse
The previous method of using the Remove link icon to remove hyperlinks might be the simplest, but manually removing hyperlinks for all the cells isn’t very efficient. However, you can remove hyperlinks for all the required cells by using options with the right click of the mouse.
Steps:
- First, select the range C5:C11. Afterward, click on the right button of your mouse, and from the pop-up options, first select View More Cell Actions and then select Remove Link.
- This will remove hyperlinks from all the required cells.
Read More: How to Use HYPERLINK Function in Google Sheets (2 Ways)
3. Applying Paste Special
Another way to remove hyperlinks from cells is to copy the data first and then apply Paste Special from the Edit ribbon.
Steps:
- First, select the range C5:C11 and copy it by using the keyboard shortcut CTRL+C.
- Keep the range C5:C11 selected and then go to the Edit ribbon. From the pop-up options, first, select Paste Special and then select Values Only.
- This will remove hyperlinks from all the cells of the selected range.
Read More: How to Get Hyperlink from Cell in Google Sheets (4 Quick Tricks)
4. Employing Formula
You can also apply a simple formula to remove hyperlink from any cell. Later you can use Autofill or Fill handle to copy the formula in other cells to remove hyperlinks.
Steps:
- To employ the formula, we require an additional column. Therefore, modify your dataset like the following.
- Afterward, select Cell D5 and type in the following formula-
=“”&C5- After that, press Enter key to get the required result.
- Use the Fill Handle icon to copy the formula in other cells of Column D.
- Finally, you have all the required values without any hyperlinks.
Read More: How to Hyperlink Data to Another Sheet with Formula in Google Sheets
5. Using Apps Script
Another way to remove all the hyperlinks from any worksheet is to write a simple script in the Apps Script module.
Steps:
- First, open your worksheet and go to the Extensions From there, select Apps Script.
- A new window like the following will open in your browser.
- Rename the project and insert the following script-
function removeHyperlink() {
var ss = SpreadsheetApp.getActiveSpreadsheet().
getActiveSheet();
var last_row = ss.getLastRow();
for (var i=5;i<=last_row;i++){
ss.getRange(i,3).setShowHyperlink(false);
}
}
- Then, Save and Run the project.
- After that, all the links from your worksheet have now been removed.
Read More: How to Rename a Hyperlink in Google Sheets (2 Quick Ways)
How to Extract URL from Hyperlink in Google Sheets
Sometimes, you might want to extract and store the URL of the hyperlink before removing it. You can attain this through some simple steps.
Steps:
- Modify your dataset like the following.
- Then, select Cell C5 and hover your mouse pointer over the cell. A pop-up of the hyperlinked page will appear. Click on the Copy Link icon from there.
- After that, paste the link in Cell D5 by using the keyboard shortcut CTRL+V.
- Finally, continue the same steps for other cells.
Read More: How to Extract URL from Hyperlink in Google Sheets (3 Ways)
Things to Be Considered
- Remove Link icon cannot be applied to a range.
- The setShowHyperlink takes a Boolean value as an argument.
- Provide necessary accesses while you Run the script in Apps Script.
Conclusion
This concludes our article to learn how to remove hyperlink from a cell in Google Sheets. I hope the article was sufficient for your requirements. Feel free to leave your thoughts on the article in the comment box. Visit our website OfficeWheel.com for more helpful articles.
