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))))

Back to top

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

More than 1300 Excel formulas

Excel categories

Leave a Comment