# How to decode URL-encoded strings

This formula decodes a URL-encoded string, replacing specific percentage symbol (%) and a hexadecimal number with non-alphanumeric characters.

Excel 365 dynamic array formula in cell C3:

How to use the ENCODEURL function

### Explaining formula

#### Step 1 - Split string into an array based on a given delimiting character (%)

The TEXTSPLIT function splits a string into an array based on delimiting values.

Function syntax: TEXTSPLIT(Input_Text, col_delimiter, [row_delimiter], [Ignore_Empty])

TEXTSPLIT(B3,,"%")

becomes

TEXTSPLIT("https%3A%2F%2Fwww.disney.com",,"%",1)

and returns

{"https";"3A";"2F";"2Fwww.disney.com"}.

#### Step 2 - Count rows in array

The ROWS function calculate the number of rows in a cell range.

Function syntax: ROWS(array)

ROWS(TEXTSPLIT(B3,,"%"))

becomes

ROWS({"https";"3A";"2F";"2Fwww.disney.com"})

and returns

4.

#### Step 3 - Create a sequence from 1 to n

The SEQUENCE function creates a list of sequential numbers.

Function syntax: SEQUENCE(rows, [columns], [start], [step])

SEQUENCE(ROWS(TEXTSPLIT(B3,,"%")))

becomes

SEQUENCE(4)

and returns

{1;2;3;4}.

#### Step 4 - Check if number is equal to 1

SEQUENCE(ROWS(TEXTSPLIT(B3,,"%")))=1

becomes

{1;2;3;4}=1

and returns

{TRUE; FALSE; FALSE; FALSE}.

#### Step 5 - Extract a substring

The MID function returns a substring from a string based on the starting position and the number of characters you want to extract.

Function syntax: MID(text, start_num, num_chars)

MID(TEXTSPLIT(B3,,"%"),1,2)

becomes

MID({"https";"3A";"2F";"2Fwww.disney.com"},1,2)

and returns

{"ht"; "3A"; "2F"; "2F"}.

#### Step 6 - Convert hex value to a number

The HEX2DEC function converts a hexadecimal number to a decimal number.

Function syntax: HEX2DEC(number)

HEX2DEC(MID(TEXTSPLIT(B3,,"%"),1,2))

becomes

HEX2DEC({"ht"; "3A"; "2F"; "2F"})

and returns

{#NUM!; 58; 47; 47}.

#### Step 7 - Convert number to character

The CHAR function converts a number to the corresponding ANSI character determined by your computers character set.

Function syntax: CHAR(text)

CHAR(HEX2DEC(MID(TEXTSPLIT(B3,,"%"),1,2)))

becomes

CHAR({#NUM!; 58; 47; 47})

and returns

{#NUM!; ":"; "/"; "/"}.

#### Step 8 - Extract the last part of each string

The MID function returns a substring from a string based on the starting position and the number of characters you want to extract.

Function syntax: MID(text, start_num, num_chars)

MID(TEXTSPLIT(B3,,"%"),3,9999999))

becomes

MID({"https";"3A";"2F";"2Fwww.disney.com"},3,9999999))

and returns

{"tps";"";"";"www.disney.com"}

#### Step 9 - Concatenate strings

The ampersand character (&) lets you concatenate strings in an Excel formula.

CHAR(HEX2DEC(MID(TEXTSPLIT(B3,,"%"),1,2)))&MID(TEXTSPLIT(B3,,"%"),3,9999999))

becomes

{#NUM!; ":"; "/"; "/"}.&{"tps";"";"";"www.disney.com"}

and returns

{#NUM!;":";"/";"/www.disney.com"}

#### Step 10 - Filter strings based on position

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

Function syntax: IF(logical_test, [value_if_true], [value_if_false])

IF(SEQUENCE(ROWS(TEXTSPLIT(B3,,"%")))=1,TEXTSPLIT(B3,,"%"),CHAR(HEX2DEC(MID(TEXTSPLIT(B3,,"%"),1,2)))&MID(TEXTSPLIT(B3,,"%"),3,9999999))

becomes

IF({TRUE; FALSE; FALSE; FALSE}, {"https";"3A";"2F";"2Fwww.disney.com"}, {#NUM!;":";"/";"/www.disney.com"})

and returns

{"https"; ":"; "/"; "/www.disney.com"}.

#### Step 11 - Join strings

The TEXTJOIN function combines text strings from multiple cell ranges.

Function syntax: TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

TEXTJOIN("",TRUE,IF(SEQUENCE(ROWS(TEXTSPLIT(B3,,"%")))=1,TEXTSPLIT(B3,,"%"),CHAR(HEX2DEC(MID(TEXTSPLIT(B3,,"%"),1,2)))&MID(TEXTSPLIT(B3,,"%"),3,9999999)))

becomes

TEXTJOIN("",TRUE,{"https"; ":"; "/"; "/www.disney.com"})

and returns

"https://www.disney.com".

#### Step 12 - Shorten formula

The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.

Function syntax: LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

TEXTJOIN("",TRUE,IF(SEQUENCE(ROWS(TEXTSPLIT(B3,,"%")))=1,TEXTSPLIT(B3,,"%"),CHAR(HEX2DEC(MID(TEXTSPLIT(B3,,"%"),1,2)))&MID(TEXTSPLIT(B3,,"%"),3,9999999)))

x - TEXTSPLIT(B3,,"%")

LET(x,TEXTSPLIT(B3,,"%"),TEXTJOIN("",TRUE,IF(SEQUENCE(ROWS(x))=1,x,CHAR(HEX2DEC(MID(x,1,2)))&MID(x,3,99999999999))))

### ASCII character set

The following table shows the ASCII character set, the corresponding hexadecimal values and the URL encoded strings. Characters from 128 and above will not work with the above formula, they contain multiple hexadecimal values.

Dec | Hex | ASCII | ENCODEURL | Dec | Hex | ASCII | ENCODEURL | Dec | Hex | ASCII | ENCODEURL | Dec | Hex | ASCII | ENCODEURL | Dec | Hex | ASCII | ENCODEURL | Dec | Hex | ASCII | ENCODEURL | Dec | Hex | ASCII | ENCODEURL |

1 | 01 | %01 | 41 | 29 | ) | %29 | 81 | 51 | Q | Q | 121 | 79 | y | y | 161 | A1 | ¡ | %C2%A1 | 201 | C9 | É | %C3%89 | 241 | F1 | ñ | %C3%B1 | |

2 | 02 | %02 | 42 | 2A | * | %2A | 82 | 52 | R | R | 122 | 7A | z | z | 162 | A2 | ¢ | %C2%A2 | 202 | CA | Ê | %C3%8A | 242 | F2 | ò | %C3%B2 | |

3 | 03 | %03 | 43 | 2B | + | %2B | 83 | 53 | S | S | 123 | 7B | { | %7B | 163 | A3 | £ | %C2%A3 | 203 | CB | Ë | %C3%8B | 243 | F3 | ó | %C3%B3 | |

4 | 04 | %04 | 44 | 2C | , | %2C | 84 | 54 | T | T | 124 | 7C | | | %7C | 164 | A4 | ¤ | %C2%A4 | 204 | CC | Ì | %C3%8C | 244 | F4 | ô | %C3%B4 | |

5 | 05 | %05 | 45 | 2D | - | - | 85 | 55 | U | U | 125 | 7D | } | %7D | 165 | A5 | ¥ | %C2%A5 | 205 | CD | Í | %C3%8D | 245 | F5 | õ | %C3%B5 | |

6 | 06 | %06 | 46 | 2E | . | . | 86 | 56 | V | V | 126 | 7E | ~ | %7E | 166 | A6 | ¦ | %C2%A6 | 206 | CE | Î | %C3%8E | 246 | F6 | ö | %C3%B6 | |

7 | 07 | %07 | 47 | 2F | / | %2F | 87 | 57 | W | W | 127 | 7F | | %7F | 167 | A7 | § | %C2%A7 | 207 | CF | Ï | %C3%8F | 247 | F7 | ÷ | %C3%B7 | |

8 | 08 | %08 | 48 | 30 | 0 | 0 | 88 | 58 | X | X | 128 | 80 | € | %E2%82%AC | 168 | A8 | ¨ | %C2%A8 | 208 | D0 | Ð | %C3%90 | 248 | F8 | ø | %C3%B8 | |

9 | 09 | %09 | 49 | 31 | 1 | 1 | 89 | 59 | Y | Y | 129 | 81 | | %C2%81 | 169 | A9 | © | %C2%A9 | 209 | D1 | Ñ | %C3%91 | 249 | F9 | ù | %C3%B9 | |

10 | 0A | %0A | 50 | 32 | 2 | 2 | 90 | 5A | Z | Z | 130 | 82 | ‚ | %E2%80%9A | 170 | AA | ª | %C2%AA | 210 | D2 | Ò | %C3%92 | 250 | FA | ú | %C3%BA | |

11 | 0B | %0B | 51 | 33 | 3 | 3 | 91 | 5B | [ | %5B | 131 | 83 | ƒ | %C6%92 | 171 | AB | « | %C2%AB | 211 | D3 | Ó | %C3%93 | 251 | FB | û | %C3%BB | |

12 | 0C | %0C | 52 | 34 | 4 | 4 | 92 | 5C | \ | %5C | 132 | 84 | „ | %E2%80%9E | 172 | AC | ¬ | %C2%AC | 212 | D4 | Ô | %C3%94 | 252 | FC | ü | %C3%BC | |

13 | 0D | %0D | 53 | 35 | 5 | 5 | 93 | 5D | ] | %5D | 133 | 85 | … | %E2%80%A6 | 173 | AD | | %C2%AD | 213 | D5 | Õ | %C3%95 | 253 | FD | ý | %C3%BD | |

14 | 0E | %0E | 54 | 36 | 6 | 6 | 94 | 5E | ^ | %5E | 134 | 86 | † | %E2%80%A0 | 174 | AE | ® | %C2%AE | 214 | D6 | Ö | %C3%96 | 254 | FE | þ | %C3%BE | |

15 | 0F | %0F | 55 | 37 | 7 | 7 | 95 | 5F | _ | _ | 135 | 87 | ‡ | %E2%80%A1 | 175 | AF | ¯ | %C2%AF | 215 | D7 | × | %C3%97 | 255 | FF | ÿ | %C3%BF | |

16 | 10 | %10 | 56 | 38 | 8 | 8 | 96 | 60 | ` | %60 | 136 | 88 | ˆ | %CB%86 | 176 | B0 | ° | %C2%B0 | 216 | D8 | Ø | %C3%98 | 256 | ||||

17 | 11 | %11 | 57 | 39 | 9 | 9 | 97 | 61 | a | a | 137 | 89 | ‰ | %E2%80%B0 | 177 | B1 | ± | %C2%B1 | 217 | D9 | Ù | %C3%99 | 257 | ||||

18 | 12 | %12 | 58 | 3A | : | %3A | 98 | 62 | b | b | 138 | 8A | Š | %C5%A0 | 178 | B2 | ² | %C2%B2 | 218 | DA | Ú | %C3%9A | 258 | ||||

19 | 13 | %13 | 59 | 3B | ; | %3B | 99 | 63 | c | c | 139 | 8B | ‹ | %E2%80%B9 | 179 | B3 | ³ | %C2%B3 | 219 | DB | Û | %C3%9B | 259 | ||||

20 | 14 | %14 | 60 | 3C | < | %3C | 100 | 64 | d | d | 140 | 8C | Œ | %C5%92 | 180 | B4 | ´ | %C2%B4 | 220 | DC | Ü | %C3%9C | 260 | ||||

21 | 15 | %15 | 61 | 3D | = | %3D | 101 | 65 | e | e | 141 | 8D | | %C2%8D | 181 | B5 | µ | %C2%B5 | 221 | DD | Ý | %C3%9D | 261 | ||||

22 | 16 | %16 | 62 | 3E | > | %3E | 102 | 66 | f | f | 142 | 8E | Ž | %C5%BD | 182 | B6 | ¶ | %C2%B6 | 222 | DE | Þ | %C3%9E | 262 | ||||

23 | 17 | %17 | 63 | 3F | ? | %3F | 103 | 67 | g | g | 143 | 8F | | %C2%8F | 183 | B7 | · | %C2%B7 | 223 | DF | ß | %C3%9F | 263 | ||||

24 | 18 | %18 | 64 | 40 | @ | %40 | 104 | 68 | h | h | 144 | 90 | | %C2%90 | 184 | B8 | ¸ | %C2%B8 | 224 | E0 | à | %C3%A0 | 264 | ||||

25 | 19 | %19 | 65 | 41 | A | A | 105 | 69 | i | i | 145 | 91 | ‘ | %E2%80%98 | 185 | B9 | ¹ | %C2%B9 | 225 | E1 | á | %C3%A1 | 265 | ||||

26 | 1A | %1A | 66 | 42 | B | B | 106 | 6A | j | j | 146 | 92 | ’ | %E2%80%99 | 186 | BA | º | %C2%BA | 226 | E2 | â | %C3%A2 | 266 | ||||

27 | 1B | %1B | 67 | 43 | C | C | 107 | 6B | k | k | 147 | 93 | “ | %E2%80%9C | 187 | BB | » | %C2%BB | 227 | E3 | ã | %C3%A3 | 267 | ||||

28 | 1C | %1C | 68 | 44 | D | D | 108 | 6C | l | l | 148 | 94 | ” | %E2%80%9D | 188 | BC | ¼ | %C2%BC | 228 | E4 | ä | %C3%A4 | 268 | ||||

29 | 1D | %1D | 69 | 45 | E | E | 109 | 6D | m | m | 149 | 95 | • | %E2%80%A2 | 189 | BD | ½ | %C2%BD | 229 | E5 | å | %C3%A5 | 269 | ||||

30 | 1E | %1E | 70 | 46 | F | F | 110 | 6E | n | n | 150 | 96 | – | %E2%80%93 | 190 | BE | ¾ | %C2%BE | 230 | E6 | æ | %C3%A6 | 270 | ||||

31 | 1F | %1F | 71 | 47 | G | G | 111 | 6F | o | o | 151 | 97 | — | %E2%80%94 | 191 | BF | ¿ | %C2%BF | 231 | E7 | ç | %C3%A7 | 271 | ||||

32 | 20 | %20 | 72 | 48 | H | H | 112 | 70 | p | p | 152 | 98 | ˜ | %CB%9C | 192 | C0 | À | %C3%80 | 232 | E8 | è | %C3%A8 | 272 | ||||

33 | 21 | ! | %21 | 73 | 49 | I | I | 113 | 71 | q | q | 153 | 99 | ™ | %E2%84%A2 | 193 | C1 | Á | %C3%81 | 233 | E9 | é | %C3%A9 | 273 | |||

34 | 22 | " | %22 | 74 | 4A | J | J | 114 | 72 | r | r | 154 | 9A | š | %C5%A1 | 194 | C2 | Â | %C3%82 | 234 | EA | ê | %C3%AA | 274 | |||

35 | 23 | # | %23 | 75 | 4B | K | K | 115 | 73 | s | s | 155 | 9B | › | %E2%80%BA | 195 | C3 | Ã | %C3%83 | 235 | EB | ë | %C3%AB | 275 | |||

36 | 24 | $ | %24 | 76 | 4C | L | L | 116 | 74 | t | t | 156 | 9C | œ | %C5%93 | 196 | C4 | Ä | %C3%84 | 236 | EC | ì | %C3%AC | 276 | |||

37 | 25 | % | %25 | 77 | 4D | M | M | 117 | 75 | u | u | 157 | 9D | | %C2%9D | 197 | C5 | Å | %C3%85 | 237 | ED | í | %C3%AD | 277 | |||

38 | 26 | & | %26 | 78 | 4E | N | N | 118 | 76 | v | v | 158 | 9E | ž | %C5%BE | 198 | C6 | Æ | %C3%86 | 238 | EE | î | %C3%AE | 278 | |||

39 | 27 | ' | %27 | 79 | 4F | O | O | 119 | 77 | w | w | 159 | 9F | Ÿ | %C5%B8 | 199 | C7 | Ç | %C3%87 | 239 | EF | ï | %C3%AF | 279 | |||

40 | 28 | ( | %28 | 80 | 50 | P | P | 120 | 78 | x | x | 160 | A0 | %C2%A0 | 200 | C8 | È | %C3%88 | 240 | F0 | ð | %C3%B0 | 280 |

### External links

Creating an Excel Formula to Encode or Unencode URLS

### Misc category

This article explains how to avoid array formulas if needed. It won't work with all array formulas, however, smaller ones […]

Table of Contents Get the latest revision Create a list with most recent data available 1. Get the latest revision […]

### Excel categories

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form