Excel convert hyperlink formulas to links with friendly names

0
votes

I need to convert columns of cells with HYPERLINK formulas into cells with just the friendly_name hyperlinked to link_location. For instance, =HYPERLINK("https://www.google.com/search?hl=en&q="&SUBSTITUTE(A2," ","+")&"&tbm=isch&gws_rd=ssl","G images") to a cell that just has G images in it and is hyperlinked to the hyperlink that the formula produces, e.g. https://www.google.com/search?hl=en&q=search+term&tbm=isch&gws_rd=ssl. I can do this by pasting into Word and pasting back into Excel, but I need to do it within Excel, whether by some copy/paste trick or a macro.

2
excelhyperlink
Can you post a sample table/output of what you're expecting to see? – BruceWayne
I want the HYPERLINK formula converted, hopefully via macro, to the same format as if I right clicked on a cell, clicked Hyperlink..., put "G images" in the "Text to display:" field and "google.com/…" in the "Address:" field. – Dave A
Ohh - So, when looking at the cell, you don't want a formula to be in the cell, but the text "G images", linked to the URL? – BruceWayne
Is the URL always going to be a Google one, like your example? – BruceWayne
I didn't see this, but no, it most definitely will not always be google. I'm starting to think I should rethink the original formulas and use a different pathway to achieve what I want, as I didn't think it would be this complicated to do what I need to do since I could so easily paste into word and back and get the results I need. Thank you very much for your help. – Dave A

2 Answers

1
votes

Found this at Chihiro, Excel Ninja: One way to do it is to copy and paste out to Word Doc. And then copy and paste back from Word Doc to Excel. This will retain link and title/friendly name.

For large documents or many columns with links, this will be less practical of course.

0
votes

I think I understand, try this (adjust rng as necessary):

edit: New code, per comment below:

Sub replace_Hyperlink_Formula_With_Text()
Dim linkWS As Worksheet, newWS As Worksheet
Dim linkText$, url$, address$, subPart$
Dim cel As Range, rng As Range
Dim commaPos&

Set linkWS = Sheets("Sheet1") ' Change this to the sheet name that has =Hyperlink("http://...","Link")
Set newWS = Sheets.Add
newWS.Name = "Links Only" ' or whatever you want the non-Hyperlink Formula sheet to be called.

Set rng = linkWS.Range("A1:A2") ' Edit this as needed

For Each cel In rng
    linkText = cel.Value
    commaPos = InStrRev(cel.Formula, ",")

    subPart = WorksheetFunction.Substitute(linkWS.Range("A2"), " ", "+")
    url = Mid(cel.Formula, WorksheetFunction.Search("(", cel.Formula) + 2, commaPos)
    Debug.Print url

    commaPos = InStrRev(url, ",")
    url = Left(url, commaPos - 2)
    url = WorksheetFunction.Substitute(url, "&", ";", 1)
    Debug.Print url


    ' Now, trim SUBSTITUTE out of it
    Dim leftUrl$, rightURL$
    leftUrl = Left(url, WorksheetFunction.Search(";", url) - 2)
    rightURL = Mid(url, WorksheetFunction.Search("&", url) + 2, Len(url))
    url = leftUrl & subPart & rightURL
    Debug.Print url
  '  cel.ClearContents
    address = cel.address
    newWS.Range(address).Hyperlinks.Add anchor:=newWS.Range(address), address:=url, TextToDisplay:=linkText
Next cel

End Sub

It's kludgy, but it should succeed in parsing out the &Substitute...& part, and replacing it with what's in A2.