How to Edit a URL in Excel With Macros

Replace Characters
1. Click the 'File' menu, click 'Options,' then click 'Customize ribbon.' Click the 'Developer' check box, then click 'OK' to close the 'Options' window. Word will display the 'Developer' tab.
2. Type the following formula into any cell in the current workbook. This formula creates a hyperlinked URL in the active cell.=HYPERLINK('http://www.whitehouse.net', 'White House')
3. Click the 'Developer' tab's 'Visual Basic' button to enter the VBA programming environment, then paste the following program into the window. This program edits a URL by using the VBA 'Replace' function. The first argument of this function is the string in which you want to make a replacement. The second argument is the text you want to replace and the third argument is the replacement text itself.Public Sub editURL()Dim URL, url2URL = ActiveCell.Formulaurl2 = Replace(URL, 'White House', 'Oval Office')ActiveCell.Formula = url2End Sub
4. Click any statement in the program, then click the 'Run' menu's 'Run' command to execute the program.
5. Click the 'Excel' icon on the taskbar to return to Excel. Notice that the 'Oval Office' text now appears in place of the original 'White House' text, indicating your program's editing of the URL was successful.
Replace Entire URL
6. Click the 'Developer' tab's 'Visual Basic' button to enter the environment for creating VBA programs.
7. Use the instructions from Step 2 of the previous section to enter any URL in a worksheet cell. For example, type '=hyperlink('http://www.whitehouse.gov', 'The Oval Office')'.
8. Paste the following program into the window. This program creates a completely new URL in the active cell.Public Sub editURL()ActiveCell.Formula = '=hyperlink(''http://www.nea.gov'', ''National Endowment for the Arts'')'End Sub
9. Click a statement in the program to select the program.
10. Click the 'Run' menu's 'Run' command to execute the program. Return the Excel by pressing 'Alt F11.' The 'White House' URL you typed in Step 2 is now replaced by a link to the home page of the National Endowment for the Arts.