How can I overcome the HYPERLINK Functions 255 character limit?

You can use Alternatively, a small VBA macro to insert a "real" Excel hyperlink, which has a much higher character limit (up to the cell's 32,767 character limit) .

This is ideal for your JotForm links because it will automatically update based on your spreadsheet data whenever you run it.

Here's a sample VBA code that creates a clickable link in the selected cell:

Sub CreateLongHyperlink()
    ' This macro creates a hyperlink in the currently selected cell
    ' It bypasses the 255-character limit of the HYPERLINK formula

    Dim targetCell As Range
    Dim longURL As String
    Dim friendlyName As String

    ' Set the cell where you want the hyperlink
    Set targetCell = ActiveSheet.Range("A1") ' <-- CHANGE THIS TO YOUR CELL

    ' --- IMPORTANT: Build your long URL here by combining strings ---
    ' This example assumes your URL parts are in cells B1, B2, etc.
    ' Replace this with your own logic to build the JotForm URL.
    longURL = Range("B1").Value & Range("B2").Value & Range("B3").Value

    ' Set the text you want to see in the cell (e.g., "Fill out Form")
    friendlyName = "Open Pre-populated Form"

    ' Delete any existing hyperlink in the cell
    On Error Resume Next
    targetCell.Hyperlinks.Delete
    On Error GoTo 0

    ' Add the new, long hyperlink to the cell
    targetCell.Parent.Hyperlinks.Add Anchor:=targetCell, _
                                     Address:=longURL, _
                                     TextToDisplay:=friendlyName

    ' Optional: Confirm it worked
    MsgBox "Hyperlink added successfully!", vbInformation

End Sub

Hope that helps.

Windows 11 now, and this is still a problem. I have a hyperlink formula on a data entry sheet, I can link to the data point address postcode on an internal mapping system, but I am limited to automatically opening only two additional map layers due to the 255 character limit. 

Metod 2 here doesn't work. I will try method 1 but I am not hopeful. Today I found a solution, basically create your greater than 255 characters url hyperlink in Word. Activate the url, with a space at the end or return. And copy and paste to a cell in Excel. 

This is only a manual solution. If you look at the link properties in Excel, the pop up menu is different to the native one in Excel. I can't seem to hold the Word formats in the cells and paste in the under 255 characters formula. 

Currently I have a helper hyperlink, above the table headers and the relevant column on the freeze pane, "Internal GIS with Useful layers. Copied from Word with about 900 characters. 

Solution was on http://www.mrexcel.com with a thread called /how-do-i-do-a-hyperlink-that-exceeds-255-chatacters-539414/