[ad_1]
Excel automatically converts URLs entered into spreadsheets to hyperlinks. You can open the websites in a browser by clicking their links in the cells. However, it isn’t always ideal to have links in spreadsheets as selecting their cells can open the websites, even when you don’t need them.
If you want to have a list of plain text URLs, this is how you can remove all hyperlinks from Excel spreadsheets.
Using the Context Menu to Remove the Hyperlink Option
If you’re using a more recent version of Excel, you can remove all the hyperlinks from a sheet with a context menu option.
- Open a blank Excel spreadsheet and enter www.google.com in cell B2.
- Then, you can right-click that cell and select the Remove Hyperlink option on the context menu. That will convert the hyperlink to a plain text URL.
- To remove multiple hyperlinks from an Excel spreadsheet, hold the Ctrl key and select several cells with URLs at once.
- Right-click on one of the selected cells and select the Remove Hyperlink option.
- To remove all hyperlinks from a sheet, press the Ctrl + A hotkey to select all the spreadsheet cells.
- Right-click on any cell and select Remove Hyperlink to convert all the links to plain text.
Removing Links From Sheets in Excel 2007
Not all Excel versions include the “Remove Hyperlink” context menu option. As such, you can’t select that option in Excel 2007. Nevertheless, 2007 users can still remove links from spreadsheets using the “Paste Special” trick.
- For example, enter www.bing.com in cell B3, then input 1 in cell C3 of the same spreadsheet. Select cell C3 and press the Ctrl + C hotkey to copy it to the Clipboard.
- Next, Right-click the cell that includes the hyperlink, otherwise B3, and select Paste Special > Paste Special… from the context menu to open the Paste Special window shown directly below.
- In the Operation section of the window, select Multiply, then press the OK button to remove the hyperlink.
Paste URLs Into Spreadsheets as Plain Text
If you need to paste many URLs into a spreadsheet, you can remove their hyperlink formatting by selecting the Keep Text Only option.
- As an example, copy the following URL: www.google.com.
- In Excel at cell D3 or your preferred cell, right-click it and select Paste Text Only (T), which is the clipboard icon with the big letter A.
- The pasted cell now shows the URL without a hyperlink.
Set up a Macro That Removes Hyperlinks
Macros are a recorded sequence of selected options. This Tech Junkie post (and its video) tells you how to record macros in Windows. The complete Excel application includes a macro-recording tool to record macros, but you can also set up macros manually by entering the Visual Basic for Applications (VBA) code. So why not set up a macro that removes all hyperlinks from an Excel sheet?
- Press the Alt + F11 hotkey to open the VB editor in Excel.
- Double-click on ThisWorkbook in the VBAProject panel.
- Copy and paste the following code into the VB code window:
Sub RemoveAllHyperlinks()
'Code by Sumit Bansal @ trumpexcel.com
ActiveSheet.Hyperlinks.Delete
End Sub - Option 1: You can run the sub in VBA if you like at this point by selecting Run at the top, but you won’t see the changes on the spreadsheet unless you move the VBA Window out of the way. Removing hyperlinks is now complete.
- Option 2: Close the VBA window and go back to the active Excel spreadsheet. Press the Alt + F8 hotkey combo to open a Macro window, select ThisWorkbook.RemoveAllHyperlinks from the list, and then press the Run button.
The macro above removes all hyperlinks from the “active” worksheet—not the workbook. However, you can alter the code to work on every sheet in the workbook and do many other things.
Switch off Automatic Hyperlinks
Excel automatically converts URLs to links, but you can configure the software so that all URLs entered remain as plain text. Here’s how to do it.
- Select the File tab and click Options to open the window directly below.
- Select Proofing on the left and press the AutoCorrect Options button.
- The AutoCorrect window appears. Select the AutoFormat As You Type tab on that window.
- Deselect the Internet and network paths with hyperlinks option.
- Press the OK button and close the Excel Options window.
Now, URLs entered in spreadsheet cells will remain text only. Shutting off automatic hyperlinks can save you a lot of unnecessary work.
In closing, you have many ways to remove some or all of the hyperlinks in an Excel spreadsheet. You can remove them individually, in a group, or all at once. Depending on your scenario and needs, you can also set Excel to never convert URLs to hyperlinks, which comes in handy if you have a lot of URL entries to enter.
From selecting a group of cells or all of them and using the right-click actions to adding VBA macros as subs and turning off automatic URL entries, you’ve got plenty of options to find one that suits your needs.
[ad_2]
Source link
