VBA – Encode URL Query Parts In Excel

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.