Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a spreadsheet with 260 cells that contain different hyperlinks. Is there a way to programmatically change the link instead of editing each link?

The 260 hyperlinks are all unique and need to be changed to other unique values. Each link has had text added to the beginning of each link. The existing link (as example) is javascript:go("http...") and I need to remove the javascript:go(" and ") from each one.

share|improve this question

2 Answers 2

I think you need to be more specific about what you want to do. Are there many hyperlinks which are all pointing to one or two locations and you want to change them all, or there are like many different ones and you are about to change each one of them to another unique hyperlink. Whatever your problem is, I think this tiny Excel add-on might help you:

kutools for excel

It is made to perform things like your problem.

There is also this forum, which works on Excel problems and you will sure get your problem solved in less than 5 hours:

Excel Forum

share|improve this answer
    
the 260 hyperlinks are all unique and need to be changed to other unique values. Each link has had text added to the beginning of each link. The existing link (as example) is javascript:go("http.........) and I need to remove the "javascript:go(" from each one –  Gail Hoefflin Mar 28 '13 at 13:44
1  
@GailHoefflin Have you tried just using the normal Find/Replace tool? It can replace values from within formulas, but I'm not sure if it will do hyperlinks. –  techturtle Mar 28 '13 at 14:13
    
Never mind. My test didn't work for Find/Replace. –  techturtle Mar 28 '13 at 14:15
    
I tried that also on the Find/Replace –  Gail Hoefflin Mar 28 '13 at 14:30

You can fix all the hyperlinks by replacing parts of their address using the following code:

Sub ClearHyperlinks()
Dim hl As Hyperlink

For Each hl In Worksheets("Sheet1").Hyperlinks
    hl.Address = Replace(hl.Address, "javascript:go(", "")
    hl.Address = Replace(hl.Address, ")", "")
Next hl

End Sub

Each Replace() statement can be used to remove specific sets of characters. In this case, the first removes javascript:go( and the second removes ). If you need additional characters removed, just incorporate them into the statements or add a new line, following the same pattern. You may need to do some tricky stuff to remove any quotes, as they are also being used by the code itself. To remove double quotes, you could change the lines to this:

hl.Address = Replace(hl.Address, "javascript:go(" & Chr(34), "")
hl.Address = Replace(hl.Address, Chr(34) & ")", "")

Chr(34) is the code for the double quote. However, when I played with this just now, Excel changed my double quotes to %22 which is the URL-sanitized version of the double quote. In that case, you would want your code to look like this:

hl.Address = Replace(hl.Address, "javascript:go(%22" , "")
hl.Address = Replace(hl.Address, "%22)", "")

To run this code, press Alt+F11 to bring up the VBA window, and paste it in to a new or existing module. If you're only going to run it once, you can just click anywhere within this code and press F5 to run it. If you'll have to run this several times, you may want to assign the macro to a button.

share|improve this answer
    
OK-I have added this code to the .xlsm file. (bear with me as I am just a user-not a coder) I also need to remove the last 2 characters of each hyperlink which are ') in each hyperlink. can this piece of code be updated to include this? Also-how do I execute the code to update the hyperlinks? –  Gail Hoefflin Mar 28 '13 at 14:55
    
@GailHoefflin See the edits that I just added to my answer. –  techturtle Mar 28 '13 at 18:04
    
thank you-worked great and saved me mass amounts of time –  Gail Hoefflin Apr 3 '13 at 12:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.