Search code examples
excelurl

Convert a URL formatted content to plain text in microsoft EXCEL

I have URL formatted content, usually I just translate manually 1-by-1m but this time there are thousand of entry, eg:-

%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

Is there any way to convert all of this content to plain English text in Microsoft Excel?

Regards

Solution

  • There isn't a built-in function to handle this, but it's possible with a custom function, installing a third-party add-in, or using the substitute command:

    Using a custom VBA function

    Source: 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

    With third-party add-in

    Source: SeoTools (needs installation)

    =UrlDecode(your_string_here)

    With substitute command

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

    Paste the formula below to the right of your cell in order to URL decode the contents of that cell

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

    Or for working with GUIDs, add one more SUBSTITUTE for the dashes.

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

    For completeness, here is the reverse formula for URL encode. This is the same as the URL encode formula but positions of new_text and old_text swapped around.

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

    Or for working with GUIDs, add one more SUBSTITUTE for the dashes.

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