How to decode URL-encoded strings

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

Get the Excel file

How-to-decode-url.xlsx

External links

Creating an Excel Formula to Encode or Unencode URLS

Misc category

Exact word in string
I read an interesting blog post Is A Particular Word Contained In A Text String? on Spreadsheetpage. That inspired me […]

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

Find positive and negative amounts that net to zero
I found this excel question: I am a Controller in a multinational company. We have many transactions (sales, credits, debits, […]

See all articles in 'misc' category

More than 1300 Excel formulas

Excel categories

Conditonal Formatting

Pivot Tables

VBA

Macros

UDFs

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