CODEWITHSUNDEEP
excel-formulahyperlink
user974061
user974061

Reputation: 363

Excel 2016 hyperlink to webpage creates error wrong data type

I have a formula in Excel that creates a url. 

https://dev.virtualearth.net/REST/V1/Imagery/Map/Road/33.344098%2C-86.92109/8?mapSize=1500,1500&mapLayer=TrafficFlow&format=png&pushpin=33.344098,-86.92109;64;2&pushpin=33.3326334378716,-86.7889690445497;64;3&pushpin=33.32602,-87.03541;64;4&pushpin=33.69447,-85.85043;64;5&pushpin=33.344098,-86.92109;64;6&pushpin=35.15039,-89.94733;64;7&pushpin=35.096314,-89.804006;64;8&pushpin=35.0512,-89.93951;64;9&pushpin=35.15098,-90.1767;64;10&key=xxxxx

If I copy/paste the result of the formula into a browser everything works fine.

If I try to create a hyperlink within Excel

HYPERLINK(C23,"Dots on a Map")

I get an error message saying wrong data type.

What needs to be modified to be able to use cell C23 as a hyperlink?

Upvotes: 0

Views: 641

Answers (1)

Wizhi
Wizhi

Reputation: 6549

The reason is that your url link has more than 255 character which the function =Hyperlink() don't allow.

If you split the link and concatenate those individual parts (less than 255 character) you can overcome that limit.

With a macro you can force that link to be clickable again. I took one I found by Brad Yundt for demonstration purpose and modified it.

Option Explicit

Sub HyperlinkMaker()
'Code assumes column A and B values will be concatenated and used to make a hyperlink
'The hyperlink will be put in column C
Dim i As Long, firstRow As Long, lastRow As Long
Dim sFriendly As String, sHyperlink As String
firstRow = 1        'Put first hyperlink on this row
With ActiveSheet
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row     'Look at last cell in column A with data
    For i = firstRow To lastRow
        sHyperlink = .Cells(i, "B").Value & .Cells(i, "B").Value & .Cells(i, "B").Value    'Build the hyperlink
        sFriendly = .Cells(i, "A").Value 'Display the "Friendly" value instead of full hyperlink
        If sHyperlink <> "" Then .Hyperlinks.Add anchor:=.Cells(5, 1), Address:=sHyperlink, TextToDisplay:=sFriendly 'Output is set to cell(1,5), i.e A5
    Next
End With
End Sub

In my example I split your link to 3 cells (B1,B2 & B3) and have the name in A1. The output result will be in A5.

enter image description here

Upvotes: 1

Related Questions