This is a quick tutorial to explain how to bulk decode URLs in Excel. Here I will talk about an Excel formula that you can use to bulk decode URLs that you encoded earlier. With the help of the this function, you can easily decode URLs right from the Excel. The function can be applied to the whole sheet you are working on. And after doing your work, you can save the changes in the same sheet or create a new XLSX file.
Although, Excel comes with an option to encode URLs using a built-in formula. However, decoding of URLs is not currently supported in Excel. And that is why I am writing this simple tutorial to easily decode URLs in Excel. You don’t really have to do anything complicated here. Just paste the VBA code of the function and start decoding URLs in Excel. You can even save the function as add-in, so you can use it later.
How to Bulk Decode URLs in Excel?
If you have ever used VBA code in Excel, then you will easily understand how to decode URLs in Excel. Previously, I used VBA code for reverse search and reverse VLookup in Excel. And now I will show you how to bulk decode URLs in Excel using a VBA formula.
Here are the steps to decode URLs in an Excel sheet.
Step 1: Open Excel and then load the sheet which contains the URLs that you want to decode. Next, open the VBA Editor. To open VBA Editor, you can press ALT+F11. The interface of VBA editor looks like this screenshot.
Step 2: Now, click Insert > Module and then a window will open in which you have to paste this code.
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
Source: Stackoverflow
Step 3: Now, save the changes and get back to the sheet. Select a cell and enter the formula “=urlDecode(cell)”. Also, you will see its name in the suggestions as well. And replace “cell” with the cell address which contain the encoded URL. You can then drag the formula along the column. See the following screenshot.
Step 4: Now, as you enter the formula, that cell will be filled with the decoded URL. You can see this in the following screenshot.
In this way, you can bulk decode URLs in Excel. And the VBA code that I have mentioned above will help you to decode URLs. You can now create your own function and start decoding URLs in just a few seconds.
Final Thoughts
At first, I didn’t believe that Excel doesn’t have the formula to decode URLs. Though, it has the built-in formula to encode URLs but I was looking for ways to decode URLs in Excel. And then I found this code and it worked like a charm. So, if you are looking for ways to decode URLs in Excel, then this tutorial will surely help you.
