Is there a formula to decode URL's?

Is there a formula i can use to decode URL's back to their original form?

Originally Posted by Richard Buttrey

Hi,

Can you clarify please. An example or three of URLs and the results you want after 'decoding' them.

An example would be this, top line is encoded and bottom line is decoded.

cid=up1hdrs459&mkwid=sMeHM1DPA_dc|pcrid|41715939743|pkw|perfume%20bcbg%20maxzaria|pmt|e|cmpn|205_Perfumes+By_BCBG%3EBrand_Search|&lp=https%3A%2F%2Fwww.perfumes.com%2Fmembership&gclid=E9Oji574CFahaMLzMAFg

cid=up1hdrs459&mkwid=sMeHM1DPA_dc|pcrid|41715939743|pkw|perfume bcbg maxzaria|pmt|e|cmpn|205_Perfumes By_BCBG>Brand_Search|&lp=https://www.perfumes.com/membership&gclid=E9Oji574CFahaMLzMAFg

No, you'll need vba

Private Declare Function UrlUnescape Lib "Shlwapi.dll" Alias "UrlUnescapeA" ( _
  ByVal pszURL As String, ByVal pszEscaped As String, ByRef pcchEscaped As Long, _
  ByVal dwFlags As Long) As Long

Public Function UnEscapeURL(ByVal URL As String) As String

Dim EscTxt As String
Dim nLen As Long

nLen = Len(URL) * 3
EscTxt = Space$(nLen)

If UrlUnescape(URL, EscTxt, nLen, &H2000000) = 0 Then
  UnEscapeURL = Left$(EscTxt, nLen)
End If
End Function

Here's the list if you fancy a formula:

Character	Escape Sequence
^	%5E
&	%26
`	%60
{	%7B
}	%7D
|	%7C
]	%5D
[	%5B
"	%22
<	%3C
>	%3E
\	%5C

Originally Posted by Kyle123

Here's the list if you fancy a formula:

Character	Escape Sequence
^	%5E
&	%26
`	%60
{	%7B
}	%7D
|	%7C
]	%5D
[	%5B
"	%22
<	%3C
>	%3E
\	%5C

Im not good with VBA so maybe im doing something wronmg. I created a macro with the first code you sent me but it does nothing am i missing this part of the code? Also I only need to decode a column of URL's not the entire workbook. Sorry if i sound dumb .

Richard the word By is not a constant in the URL strings.

No you didn't miss anything. What have you actually done with the code posted?

It should go something like this:
How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To use the code, just use it as a normal function:

PHP Code: 

=UnEscapeUrl(A1) 


Originally Posted by Kyle123

No you didn't miss anything. What have you actually done with the code posted?

It should go something like this:
How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To use the code, just use it as a normal function:

PHP Code: 

=UnEscapeUrl(A1) 


Woot this works almost like a charm but its not removing the + symbols (Perfumes+By) should be (Perfumes By), how can i fix this thanks sooo much.

Anyone know why this doesnt decode the +?

Never mind i found one that works online for those of you looking for this fix. This one will encode or decode, once you enter the macro you just type =urldecode in a cell and point to the cell containing the URL you need to decode.

Public Function URLEncode(StringToEncode As String, Optional _
UsePlusRatherThanHexForSpace As Boolean = False) As String

Dim TempAns As String
Dim CurChr As Integer
CurChr = 1
Do Until CurChr - 1 = Len(StringToEncode)
Select Case Asc(Mid(StringToEncode, CurChr, 1))
Case 48 To 57, 65 To 90, 97 To 122
TempAns = TempAns & Mid(StringToEncode, CurChr, 1)
Case 32
If UsePlusRatherThanHexForSpace = True Then
TempAns = TempAns & "+"
Else
TempAns = TempAns & "%" & Hex(32)
End If
Case Else
TempAns = TempAns & "%" & _
Format(Hex(Asc(Mid(StringToEncode, _
CurChr, 1))), "00")
End Select

CurChr = CurChr + 1
Loop

URLEncode = TempAns
End Function


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

hi!

Sorry...

I found the solution for my question, can I write this on the same topic?

so, I created this thread:
excelforum.com/excel-formulas-and-functions/1261747-is-there-a-formula-to-decode-urls-and-having-outputting-in-utf-8-a.html
that's the way?

You can do it without VBA using array formulas. If A1 is the cell to be decoded, enter this formula, and then press Ctrl-Shift-Enter:

=TEXTJOIN("", FALSE,
    MID(A1,
        FIND("*",
            SUBSTITUTE("%DD"&A1,"%","*",  ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,"%",""))+1))   )
        ),
        FIND("*",
            SUBSTITUTE(A1&"%","%","*",    ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,"%",""))+1))   )
        ) - FIND("*",
            SUBSTITUTE("%EE"&A1,"%","*",  ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,"%",""))+1))   )
        )
    ) & IFERROR(CHAR(HEX2DEC(MID(A1,
        FIND("*",
            SUBSTITUTE(A1&"%","%","*",    ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,"%",""))+1))   )
        )+1,
       2
    ))),"")
 )

It won't work if you don't Ctrl-Shift-Enter. The URL string in A1 must be valid and have no * characters (otherwise, consider changing the expression above). For example, a sequence like %A% will make the expression not work.