After setting hyperlinks in Excel you can easily edit them or find and replace them using some features of Excel. This article will provide you with 3 quick and useful methods to find and replace hyperlinks in Excel with sharp steps and clear illustrations.
Download Practice Workbook
You can download the free Excel template from here and practice on your own.
3 Ways to Find and Replace Hyperlinks in Excel
Let’s get introduced to our dataset first that represents some article’s IDs and corresponding websites.
1. Find and Replace All Hyperlinks in Excel
In our very first method, we’ll find all the hyperlinks in a sheet and then will replace them using the Find and Replace tool. From my dataset, I’ll replace the website address with the website name ‘ExcelDemy’.
Steps:
- Next, click on the drop-down icon beside Format from the Find what.
- Then from the list click Choose Format From Cell.
Soon after, you will see a plus icon with a dropper sign in your cursor.
- Click any cell that contains a hyperlink.
- Type the text by which you want to replace in Replace with box.
- Finally, just press Replace All.
Now see that all the links are replaced.
Read More: Find External Links in Excel (6 Quick Methods)
2. Find and Replace Specific Text from Hyperlinks
Here, we’ll find and replace a specific text from hyperlinks. For that, I have modified the dataset and inserted some article links. Now I’ll find the text ‘exdemy’ from the links and then will replace them with ‘exceldemy’.
Steps:
- First, follow the first five steps to find the hyperlinks.
- Then type exdemy in the Find what box and exceldemy in the Replace with box.
- Finally, just press Replace All.
Then you will get that Excel has replaced the text from the hyperlinks.
Read More: How to Convert Text to Hyperlink in Excel (5 Methods)
Similar Readings
- How to Break Links in Excel (3 Quick Methods)
- VBA to Add Hyperlink to Cell Value in Excel (4 Criteria)
- How to Update Hyperlink in Excel Automatically (2 Ways)
- Excel VBA: Open Hyperlink in Chrome (3 Examples)
- Excel Hyperlink to Cell in Another Sheet with VLOOKUP (With Easy Steps)
3. Find and Replace Hyperlinks Using VBA in Excel
Here, I inserted some hyperlinks with the article IDs. And now we’ll apply VBA to find and replace specific text. See that there is a text ‘exdemy’ in the hyperlinks, we’ll replace it with ‘exceldemy’.
Steps:
- Press Alt + F11 to open the VBA window.
- Then click as follows: Insert ➤ Module.
- Later, type the following codes in the module-
Sub Find_Replace_Hyperlinks()
Dim xWS As Worksheet
Dim xLink As Hyperlink
Dim OldLink As String, NewLink As String
Box_Title = "Find & Replace Hyperlinks"
Set xWS = Application.ActiveSheet
OldLink = Application.InputBox("Input Old Text:", Box_Title, "", Type:=2)
NewLink = Application.InputBox("Input New Text:", Box_Title, "", Type:=2)
Application.ScreenUpdating = False
For Each xLink In xWS.Hyperlinks
xLink.Address = Replace(xLink.Address, OldLink, NewLink)
Next
Application.ScreenUpdating = True
End Sub
Code Breakdown
- Here, I created a Sub procedure Find_Replace_Hyperlinks.
- Then declared some variables– xWS As Worksheet, xLink As Hyperlink, OldLink As String, and NewLink As String.
- Next, mentioned box title- “Find & Replace Hyperlinks“.
- Then used ActiveSheet to select the activesheet.
- After that, used InputBox(“Input Old Text:”, Box_Title, “”, Type:=2) to input the old text and used Application.InputBox(“Input New Text:”, Box_Title, “”, Type:=2) to input the new text.
- Later, I used the For Loop to go through each Hyperlink to find the old text then used Replace to replace the old text with the new text.
- Next, press the Run icon to run the codes.
- At this moment, type the text that you want to replace and press OK.
- Here, I typed exdemy.
- Then type the replacing text which is exceldemy.
- Finally, just press OK.
Now have a look, the text is replaced successfully.
Read More: How to Get Hyperlink from an Excel Cell with VBA (3 Methods)
Practice Section
You will get a practice sheet in the Excel file given above to practice the explained ways.
Conclusion
I hope the procedures described above will be good enough to find and replace hyperlinks in Excel. Feel free to ask any question in the comment section and please give me feedback.
Related Articles
- How to Copy Hyperlink in Excel (4 Easy Methods)
- Excel Hyperlink with Shortcut Key (3 Uses)
- [Fix]: Excel Edit Links Change Source Not Working
- [Fix]: Excel Automatic Update of Links Has Been Disabled
- How to Hyperlink Multiple PDF Files in Excel (3 Methods)
- Why Do My Excel Links Keep Breaking? (3 Reasons with Solutions)