Removing hyperlinks from a column

[Patricia O'Toole]

I selected all of them, right clicked and selected "Remove hyperlinks". It only worked on about 10% of them. Future efforts in chunks didn't work. 

This spreadsheet is a download from Visa and is about 1500 rows long.

I also tried moving the entire column and copying the column to an entirely new workbook. Nope.

[#2 · Accepted solution]

@Patricia O'Toole 

If you are having trouble removing hyperlinks from a large number of cells in Excel and the "Remove Hyperlinks" feature is not working as expected, you can try some alternative methods:

Method 1: Using a Formula

1. Insert a new column next to the column containing the hyperlinks (e.g., if your hyperlinks are in column A, insert a new column B).
2. In the first cell of the new column (e.g., cell B2), enter the following formula:

=HYPERLINK(A2, "")

This formula essentially creates a new hyperlink with a blank URL, effectively removing the hyperlink.

1. Copy this formula down to fill the entire column for all the rows with hyperlinks.
2. Now, select and copy the entire column B (the one with the formulas).
3. Right-click on the same column and choose "Paste Special."
4. In the "Paste Special" dialog box, select "Values" and click "OK."

This process will replace the hyperlinks in column A with their respective values (text). You can then delete the original column with the hyperlinks if you wish.

Method 2: Using VBA Macro

If you are dealing with a very large number of hyperlinks, using a VBA macro can be more efficient:

1. Press ALT + F11 to open the Visual Basic for Applications (VBA) editor.
2. In the VBA editor, click Insert > Module to insert a new module.
3. Copy and paste the following VBA code into the module window:

vba code:

Sub RemoveHyperlinks()
    Dim cell As Range
    
    For Each cell In Selection
        If cell.Hyperlinks.Count > 0 Then
            cell.Hyperlinks.Delete
        End If
    Next cell
End Sub

1. Close the VBA editor.
2. Select the range of cells (the entire column or the specific cells with hyperlinks) from which you want to remove hyperlinks.
3. Press ALT + F8 to open the "Macro" dialog box.
4. Select the "RemoveHyperlinks" macro and click "Run."

This VBA macro will remove hyperlinks from the selected range of cells.

Please make sure to save your Excel file before using VBA macros, as they cannot be undone, and it is a good practice to have a backup in case something goes wrong. The text and steps were edited with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark them as helpful and like it!

This will help all forum participants.

[#3]

@NikolinoDE  Thank you so very much for all this help...and choices. I am sure this will be useful to many people and I greatly appreciate you taking the time to provide all this information.