How to remove hyperlinks without removing formatting?
In Excel, when we delete the hyperlinks which have some formatting, such as background color, font, size and so on, the formatting will be removed as well as following screenshot shown. But, sometimes, we need to keep the cell formatting when removing the hyperlinks. Are there any ways to help us to solve this annoyed problem?
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
In Excel, there is no direct way to deal with this task, but you can apply the following VBA code to preserve the cell formatting when removing hyperlinks.
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Remove hyperlinks without removing formatting
Sub RemoveHlinks() 'Update 20141024 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 = "KutoolsforExcel" 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
3. Then press F5 key to run this code, and a prompt box will pop out to remind you selecting the cells which contains the hyperlinks, see screenshot:
4. And then click OK to close the dialog, the hyperlinks are removed, but the formatting of the hyperlinks (including the underlines) is remained.