excel - 在 Microsoft EXCEL 中将 URL 格式的内容转换为纯文本

Question

我有 URL 格式的内容，通常我只是手动翻译 1×1m 但这次有数千个条目，例如:-

%E5%B7%B2%E4%BB%8E%E5%B8%90%E6%88%B7zh*****%40outlook.com%E5%88%A0%E9%99%A48618650533*%E3%80%82%E4%B8%8D%E6%98%AF%E4%BD%A0%EF%BC%9F
%E7%AE%A1%E7%90%86%E9%A2%84%E8%AE%A2%0A
https%3A%2F%2Faccount.live.com%2Fa
OTO+GLOBAL+Certification+No%3A%5B6198%5D
Deluxe+Room+-1+%E9%97%B4%0A
Ihre+Agoda+Buchung+Nr.+77083713+ist+bes %C3%A4tigt%21+Verwalten+Sie+Ihre+B
%E6%82%A8%E7%9A%84Agoda%E8%AE%A2%E5%8D%95%2877083753%29%E5%B7%B2%E7%A%AE%E8%AE%A4%EF%BC%81+%E4%BD%BF%E7%94%A8%E6%88%91%E4%BB%AC%E7%9A%84%E5%85%8D%E8%B4%B9%E5%AE%A2%E6%88%B7%E7%AB%AFhttp%3A%2F%2Fapp-agoda.com%2FGetTheApp%EF%BC%8C%E8%BD%BB%E6%9D%BE

有什么方法可以将所有这些内容转换为 Microsoft Excel 中的纯英文文本吗？

问候

Answer 1

没有内置函数来处理这个问题，但可以使用自定义函数、安装第三方插件或使用替代命令:

使用自定义 VBA 函数

来源:http://www.freevbcode.com/ShowCode.asp?ID=1512

Public Function URLDecode(StringToDecode As String) As String

Dim TempAns As String
Dim CurChr As Integer

CurChr = 1

Do Until CurChr - 1 = Len(StringToDecode)
  Select Case Mid(StringToDecode, CurChr, 1)
    Case "+"
      TempAns = TempAns & " "
    Case "%"
      TempAns = TempAns & Chr(Val("&h" & _
         Mid(StringToDecode, CurChr + 1, 2)))
       CurChr = CurChr + 2
    Case Else
      TempAns = TempAns & Mid(StringToDecode, CurChr, 1)
  End Select

CurChr = CurChr + 1
Loop

URLDecode = TempAns
End Function

使用第三方插件

来源:SeoTools (需要安装)

=UrlDecode(your_string_here)

使用替换命令

来源:https://searchmarketingcorner.wordpress.com/2013/03/27/creating-an-excel-formula-to-encode-or-unencode-urls/

将下面的公式粘贴到单元格的右侧，以便 URL 解码该单元格的内容

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CELL_TO_BE_DECODED,"%3F","?"),"%20"," "),"%25", "%"),"%26","&"),"%3D","="),"%7B","{"),"%7D","}"),"%5B","["),"%5D","]")

或者为了使用 GUID，为破折号再添加一个 SUBSTITUTE。

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CELL_TO_BE_DECODED,"%3F","?"),"%20"," "),"%25", "%"),"%26","&"),"%3D","="),"%7B","{"),"%7D","}"),"%5B","["),"%5D","]"),"%2D","-")

为了完整起见，这里是 URL 编码的反向公式。这与 URL 编码公式相同，但 new_text 和 old_text 的位置交换了。

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CELL_TO_BE_ENCODED,"?","%3F")," ","%20"),"%","%25"),"&","%26"),"=","%3D"),"{","%7B"),"}","%7D"),"[","%5B"),"]","%5D")

或者为了使用 GUID，为破折号再添加一个 SUBSTITUTE。

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CELL_TO_BE_ENCODED,"?","%3F")," ","%20"),"%","%25"),"&","%26"),"=","%3D"),"{","%7B"),"}","%7D"),"[","%5B"),"]","%5D"),"-","%2D")