- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Remove Hyperlinks Without Removing Formatting?
Links can help you access different places, websites, or files more quickly. However, there may be times when you want to keep the original formatting while removing hyperlinks from your Excel worksheet. It can take a while to manually remove hyperlinks, especially in huge datasets.
By the end of the tutorial, regardless of your level of Excel proficiency, you'll have the abilities required to competently delete hyperlinks from your Excel sheets while preserving the appearance of your data. So let's get started and see how to effectively delete hyperlinks from Excel without damaging formatting!
Remove Hyperlinks Without Removing Formatting
Here, we will first create a VBA module and then run it to complete the task. So let us see a simple process to know how you can remove hyperlinks without removing formatting in Excel.
Step 1
Consider an Excel sheet where you have a list of hyperlinks.
First, right-click on the sheet name and select View code to open the VBA application.
Right-click > View Code.
Step 2
Then click on Insert and select Module, then copy the below code into the text box.
Insert > Module > Copy.
Code
Sub RemoveHlinks() Dim Rng As Range Dim WorkRng As Range Dim TempRng As Range Dim UsedRng As Range Dim xLink As Hyperlink On Error Resume Next xTitleId = "Remove Hyperlinks Formatting" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8) Set UsedRng = Application.ActiveSheet.UsedRange For Each xLink In WorkRng.Hyperlinks Set TempRng = Cells(1, UsedRng.Column + UsedRng.Columns.Count) Set Rng = xLink.Range Rng.Copy TempRng Rng.ClearHyperlinks Set TempRng = TempRng.Resize(Rng.Rows.Count, Rng.Columns.Count) TempRng.Copy Rng.PasteSpecial xlPasteFormats TempRng.Clear Next End Sub
Step 3
Then click F5 to run the module. Then select the range of cells and click OK to complete the task.
F5 > Select Cells > OK
This is how you can remove hyperlinks in Excel.
Conclusion
In this tutorial, we have used a simple example to demonstrate how you can remove hyperlinks without removing formatting in Excel to highlight a particular set of data.