用EXCEL实现URL Encode转码

在做广告推广时，如果该网站添加的是GA监测，那么不论是硬广还是SEM，通常都会在广告链接上加入utm参数已标记该次访问用户的来源。

那么问题就来了：如果我需要在界面上显示中文(比如标记的关键词)，该怎么做呢？GA默认是不会识别参数中的中文的，如果直接添加标记的话后果会比较严重，会导致你GA后台满目疮痍，显示出一堆的乱码，运气好的时候还能费点劲分辨出来，运气不好的话就没办法看报告了。这个时候我们就需要将要标记的内容进行encode转码。在网上有很多的工具可以做到转码，但是批量的基本没有，但是EXCEL的强大之处在这个时候就体现出来了，通过VB定义一个函数，然后像=SUM一样轻松地调用这个函数，就可以愉快地进行批量转码标记了，绝对是SEM同学居家旅行之必备良药。话不多说，我们开始：

操作其实很简单，几步可以搞定：

一、新建一个EXCEL文件，另存为-保存类型-Excel 启用宏的工作簿

二、找到“开发工具”选项卡，选择Visual Basic（如果没有该选项卡，则在开始-选项-自定义功能区，将<开发工具>勾上就好）

三、回到界面，选择Visual Basic

四、在Visual Basic界面中，选择插入-模块

 

五、在输入框内输入以下代码：

Public Function UrlEncode(ByRef szString As String) As String
       Dim szChar   As String
       Dim szTemp   As String
       Dim szCode   As String
       Dim szHex    As String
       Dim szBin    As String
       Dim iCount1  As Integer
       Dim iCount2  As Integer
       Dim iStrLen1 As Integer
       Dim iStrLen2 As Integer
       Dim lResult  As Long
       Dim lAscVal  As Long
       szString = Trim$(szString)
       iStrLen1 = Len(szString)
       For iCount1 = 1 To iStrLen1
           szChar = Mid$(szString, iCount1, 1)
           lAscVal = AscW(szChar)
           If lAscVal >= &H0 And lAscVal <= &HFF Then
              If (lAscVal >= &H30 And lAscVal <= &H39) Or _
                 (lAscVal >= &H41 And lAscVal <= &H5A) Or _
                 (lAscVal >= &H61 And lAscVal <= &H7A) Then
                 szCode = szCode & szChar
              Else

                 szCode = szCode & "%" & Hex(AscW(szChar))
              End If
           Else
              szHex = Hex(AscW(szChar))
              iStrLen2 = Len(szHex)
              For iCount2 = 1 To iStrLen2
                  szChar = Mid$(szHex, iCount2, 1)
                  Select Case szChar
                         Case Is = "0"
                              szBin = szBin & "0000"
                         Case Is = "1"
                              szBin = szBin & "0001"
                         Case Is = "2"
                              szBin = szBin & "0010"
                         Case Is = "3"
                              szBin = szBin & "0011"
                         Case Is = "4"
                              szBin = szBin & "0100"
                         Case Is = "5"
                        szBin = szBin & "0101"
                         Case Is = "6"
                              szBin = szBin & "0110"
                         Case Is = "7"
                              szBin = szBin & "0111"
                         Case Is = "8"
                              szBin = szBin & "1000"
                         Case Is = "9"
                              szBin = szBin & "1001"
                         Case Is = "A"
                              szBin = szBin & "1010"
                         Case Is = "B"
                              szBin = szBin & "1011"
                         Case Is = "C"
                              szBin = szBin & "1100"
                         Case Is = "D"
                              szBin = szBin & "1101"
                         Case Is = "E"
                              szBin = szBin & "1110"
                         Case Is = "F"
                              szBin = szBin & "1111"
                         Case Else
                  End Select
              Next iCount2
              szTemp = "1110" & Left$(szBin, 4) & "10" & Mid$(szBin, 5, 6) & "10" & Right$(szBin, 6)
              For iCount2 = 1 To 24
                  If Mid$(szTemp, iCount2, 1) = "1" Then
                     lResult = lResult + 1 * 2 ^ (24 - iCount2)
                  Else: lResult = lResult + 0 * 2 ^ (24 - iCount2)
                  End If
              Next iCount2
              szTemp = Hex(lResult)
                    szCode = szCode & "%" & Left$(szTemp, 2) & "%" & Mid$(szTemp, 3, 2) & "%" & Right$(szTemp, 2)
           End If
           szBin = vbNullString
           lResult = 0
       Next iCount1
       UrlEncode = szCode
End Function

粘贴代码以后直接CTRL+S保存即可。

四、回到Excel界面，在任意单元格输入公式=URLencode(‘你选择的单元格地址’) ，就可以直接输出转码后的结果了，

赶快试试吧！