Have you ever had a column text for which you wish you could easily create links? For example, let’s say that you have a column of search words (or phrases) that should link to the Google page for each search. How would you do it? First of all, you would probably want to be able to build the URL in a separate column. Unfortunately, Excel doesn’t provide a way of encode URL parameters. Therefore, you can use the following as your own UDF:
Public Function encodeURL(ByVal queryPart As String)
Dim c As String
While Len(queryPart) > 0
c = Left(queryPart, 1)
queryPart = Mid(queryPart, 2, Len(queryPart) - 1)
If c Like "[A-Za-z0-9._~-]" Then
encodeURL = encodeURL & c
ElseIf c = " " Then
encodeURL = encodeURL & "+"
Else
encodeURL = encodeURL & "%" & Right("0" & Hex(Asc(c)), 2)
End If
Wend
End Function
That function will take in one string and output the equivalent as an encoded URL query part. That function should be the equivalent of PHP’s urlencode() function. Tomorrow, we will see how this function can actually help you to be able to create links for an entire column.
1 Comment
Graham-S · May 10, 2019 at 12:53 PM
Thanks. Got office 2010 and coding for an office 2007 user so encodeURL worksheet function not available, but this will be easily modified as the core for a Google search function