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

RokBoard1

New Member
Joined
May 28, 2021
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
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:
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.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

severynm

Board Regular
Joined
Jan 8, 2021
Messages
220
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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

Well-known Member
Joined
Jul 11, 2018
Messages
603
Office Version
  1. 365
Platform
  1. Windows
So, the short answer is:- Put the concatenated URL in Cell A1 (or wherever) and run this VBA code:
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:
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   ThisWorkbook.FollowHyperlink Target.Value
End Sub
 
Last edited:

Forum statistics

Threads
1,141,816
Messages
5,708,751
Members
421,588
Latest member
Wawie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top