Trying to create a sheet that create custom Google search links, how do I get past the the 255 characters limit for hyperlinks?

[RokBoard1]

I'm trying to build a sheet where it would create custom Google search links, for example:

In Column 1, I would insert the first string of search terms: Apple, Orange, Grapes
In Column 2, I insert the second string of search terms: Ice Cream, Juice

And it should automatically put them into a search string with boolean for review, like:

("Apple" AND "Orange" AND "Grapes") AND ("Ice cream" OR "Juice")

Then I will have a final cell that automatically generates a hyperlinked URL for Google using something like:

=HYPERLINK("http://www.google.com/search?q="&L3&E3&L7,"Custom Search"

The goal is to save time, since it does become quite a chore when there are many search terms putting them in quotes and adding boolean for each word. It's also not easier going back and forth in the tiny search bar looking for terms you want to change in a long search string.

So here is the problem:

The sheet I made works up to a certain point, around 8 words total, then the hyperlink generated returns with the VALUE error for I'm guessing too many characters in the URL.

I manually made a search URL for longer than 8 words, which confirms that it works so it's not Google but rather Excel that doesn't like it to be over a certain limit.

I tried putting the google address in another cell and just reference it in the =HYPERLINK formula, but that only saves so many characters where I can maybe put in an extra word or two.

Any pointers would be great!


Note: I am a beginner at VBA, I tried a solution for a similar problem posted on this forum HERE. Followed the comments step by step but I'd get Runtime Error 13 and debugging shows a problem in Line 20. Maybe I did something wrong, I'm not sure.

 

[severynm]

None of the solutions in that thread worked for me either, but this did: Exceeding Max Char Limit in Excel

I made a small tweak to make it work on the currently selected cell, but other than that it should be a good starting point for you.

Sub InsertVeryLongHyperlink()
    Dim currentCell As range
    Dim longHyperlink As String

    Set currentCell = ActiveCell   ' or use any cell-reference
    longHyperlink = "https://www.google.com/search?q=https%3A%2F%2Fwww.mrexcel.com%2Fboard%2Fthreads%2Ftrying-to-create-a-sheet-that-create-custom-google-search-links-how-do-i-get-past-the-the-255-characters-limit-for-hyperlinks.1172360%2F&client=firefox-b-1-d&sxsrf=ALeKk01jhWjkQa6QlDM2fCFrDCcVPiWm_Q%3A1622569235371&ei=E3G2YLSFFtmstQaCh5vgAg&oq=https%3A%2F%2Fwww.mrexcel.com%2Fboard%2Fthreads%2Ftrying-to-create-a-sheet-that-create-custom-google-search-links-how-do-i-get-past-the-the-255-characters-limit-for-hyperlinks.1172360%2F&gs_lcp=Cgdnd3Mtd2l6EAMyBwgAEEcQsAMyBwgAEEcQsAMyBwgAEEcQsAMyBwgAEEcQsAMyBwgAEEcQsAMyBwgAEEcQsAMyBwgAEEcQsAMyBwgAEEcQsANQ1X1Y1X1gh4YBaAFwAngAgAFfiAFfkgEBMZgBAaABAqABAaoBB2d3cy13aXrIAQjAAQE&sclient=gws-wiz&ved=0ahUKEwj02pa3_fbwAhVZVs0KHYLDBiwQ4dUDCA0&uact=5"  ' Or a Cell reference like [C1]

    currentCell.Hyperlinks.Add _
        Anchor:=currentCell, _
        Address:=longHyperlink, _
        SubAddress:="", _
        ScreenTip:=" - Click here to follow the hyperlink", _
        TextToDisplay:="Long Hyperlink"
End Sub

 

[Dan_W]

So, the short answer is:- Put the concatenated URL in Cell A1 (or wherever) and run this VBA code:

Sub GoToURL()
    ThisWorkbook.FollowHyperlink Address:=Range("A1").value            ' Change the A1 here depending on which cell contains the URL
End Sub

This was one of the solutions in one of the answers listed further down that StackOverflow page, but the person said that there was something wrong with it because it inexplicably loaded the site twice. His/her problem was this code doesn't create or require a hyperrlink in order to run - just a URL. That person would've been clicking on a hyperlink (which loaded the site the first time) that then triggered the VBA code which then loaded the site the second time. But the other answers on that page seem to be completely overthinking the problem. Though, to be fair, their responses were from over a decade ago and it was with an older version of Excel. In any event, that doesn't seem to be its behaviour in Excel today.

The one caveat about the above solution is that it doesn't create a hyperlink, so you need to trigger the subroutine another way - like a button, etc. There are a dozen different ways you can trigger it, but if yo uabsolutely need a hyperlink, then you can just use the same code as above. Despite what the Stackoverflow commenter said, it doesn't load the site for me twice, so the revised code (which needs to go in the sheet module of the relevant worksheet could be:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   ThisWorkbook.FollowHyperlink Target.Value
End Sub