BASE64ENCODE

[felixstraube]

Based on Theory of this page:
Sunshine2k's homepage - Understanding and implementing Base64

BASE64ENCODE function encodes string with Base64 encoding.

=LAMBDA(x, 
    LET( inputStr, x, 
        chars, MID(inputStr, SEQUENCE(,LEN(inputStr)),1), t, CONCAT(DEC2BIN(CODE(chars),8)), 
        base64codes, {"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K";"L";"M";"N";"O";"P";"Q";"R";"S";"T";"U";"V";"W";"X";"Y";"Z";"a";"b";"c";"d";"e";"f";"g";"h";"i";"j";"k";"l";"m";"n";"o";"p";"q";"r";"s";"t";"u";"v";"w";"x";"y";"z";"0";"1";"2";"3";"4";"5";"6";"7";"8";"9";"+";"/"}, 
        b, MID(t, SEQUENCE(,LEN(t)),1), 
        r, WRAPROWS(b, 6,0), 
        base64chars, BYROW(r, LAMBDA(x, CONCAT(x))), 
        CONCAT(INDEX(base64codes, BIN2DEC(base64chars)+1)) 
    )
)

Base64EncodeDecode.xlsx
	A	B
1	Input String	LAMBDA Encode
2	Sun	U3Vu
3	Office Communications Server 2007	T2ZmaWNlIENvbW11bmljYXRpb25zIFNlcnZlciAyMDA3
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=BASE64ENCODE(A2)

Lambda Functions
Name	Formula
BASE64ENCODE	=LAMBDA(x, LET( inputStr, x, chars, MID(inputStr, SEQUENCE(,LEN(inputStr)),1), t, CONCAT(DEC2BIN(CODE(chars),8)), base64codes, {"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K";"L";"M";"N";"O";"P";"Q";"R";"S";"T";"U";"V";"W";"X";"Y";"Z";"a";"b";"c";"d";"e";"f";"g";"h";"i";"j";"k";"l";"m";"n";"o";"p";"q";"r";"s";"t";"u";"v";"w";"x";"y";"z";"0";"1";"2";"3";"4";"5";"6";"7";"8";"9";"+";"/"}, b, MID(t, SEQUENCE(,LEN(t)),1), r, WRAPROWS(b, 6,0), base64chars, BYROW(r, LAMBDA(x, CONCAT(x))), CONCAT(INDEX(base64codes, BIN2DEC(base64chars)+1)) ))

 

[felixstraube]

First time posting in the lambda forum. Hope I did it right. If not, let me know.

 

[smozgur]

First time posting in the lambda forum. Hope I did it right. If not, let me know.

I edited your post to include the lambda function and make the title all upper case. I also edited the XL2BB sample range to start it from A1. It is the best for posting XL2BB mini-sheet considering the reader would likely paste it in the worksheet starting from A1 cell.
I will also edit the decode version.

Thanks for sharing your lambda function.

 

[felixstraube]

Thanks. Will remember it for next time.

 

[Xlambda]

Hi Felix, cool function. Great challenge.
Found a short YT that explains the concept of Base64 Encoding.

Noticed that your function BASE64ENCODE does not pad "=" signs at the end when necessary. (when nr. chars to be encoded are not multiple of 3)
Also, BASE64DECODE does not work properly with a code that has this "=" signs.
Easy check with this online converter (see attached picture padding "=" examples): Base64 Converter
This is my take of both functions that solves this problem and can also handle 2D arrays, not only cells.
Since we are going to use it twice, we will define name "sf" (sixty-four) for Base64 "alphabet" with a simple formula:
sf

=LET(s,SEQUENCE(64),CHAR(IFS(s<27,s+64,s<53,s+70,s=63,43,s=64,47,1,s-5)))

( Using "sf", a function that does what your function does (not "=" signs and not handling arrays, can look like this, single nested formula, no variables:
=LAMBDA(t,CONCAT(INDEX(sf,BIN2DEC(BYROW(WRAPROWS(TOCOL(MID(BASE(CODE(MID(t,SEQUENCE(LEN(t)),1)),2,8),SEQUENCE(,8),1)),6,0),CONCAT))+1))) )

B64E(a) Base64 Encoding
a: any array 1D, 2D

=LAMBDA(a,
    MAP(a, LAMBDA(x, CONCAT(INDEX(sf, BIN2DEC(BYROW(WRAPROWS(TOCOL(MID(BASE(CODE(MID(x, SEQUENCE(LEN(x)), 1)), 2, 8), SEQUENCE(, 8), 1)), 6, 0), CONCAT)) + 1)))) &
        REPT("=", MOD(LEN(a), 3))
)

B64D(a) Base64 Decoding
a: any array 1D, 2D

=LAMBDA(a,
    MAP(a, LAMBDA(x, CONCAT(IFERROR(CHAR(BIN2DEC(BYROW(WRAPROWS(TOCOL(MID(DEC2BIN(XMATCH(CODE(MID(x, SEQUENCE(LEN(x)), 1)), CODE(sf)) - 1, 6), SEQUENCE(, 6), 1), 2), 8, 0), CONCAT))), ""))))
)

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		defined name sf (sixty four) 2 ways, 2 formulas
2	sf 1	C5	=LET(s,SEQUENCE(64),CHAR(IFS(s<27,s+64,s<53,s+70,s=63,43,s=64,47,1,s-5)))
3	2	E5	=LET(a,CHAR(SEQUENCE(26,,65)),VSTACK(a,LOWER(a),SEQUENCE(10)-1,"+","/"))
4
5		0	A		A		BASE64ENCODE, does not add "=" at the end where is needed, wrong resuls if input is an array
6		1	B		B
7		2	C		C		a	YQ	=BASE64ENCODE(G7)		=BASE64ENCODE(G7:G12)
8		3	D		D		aa	YWE	=BASE64ENCODE(G8)		YWFhYWFh
9		4	E		E		aaa	YWFh	=BASE64ENCODE(G9)
10		5	F		F		aaaa	YWFhYQ	=BASE64ENCODE(G10)
11		6	G		G		aaaaa	YWFhYWE	=BASE64ENCODE(G11)
12		7	H		H		aaaaaa	YWFhYWFh	=BASE64ENCODE(G12)
13		8	I		I
14		9	J		J			single cell
15		10	K		K			=B64E(G7:G12)			Conversion dec -> 64 ; 64 -> dec
16		11	L		L			YQ=			dec	64
17		12	M		M			YWE==				=INDEX(sf,K18+1)
18		13	N		N			YWFh			2	C
19		14	O		O			YWFhYQ=
20		15	P		P			YWFhYWE==			64	dec
21		16	Q		Q			YWFhYWFh				=XMATCH(CODE(K22),CODE(sf))-1
22		17	R		R						C	2
23		18	S		S			single cell				=XMATCH(CODE(K24),CODE(sf))-1
24		19	T		T			=B64D(H16#)			c	28
25		20	U		U			a
26		21	V		V			aa			decoding is case sensitive
27		22	W		W			aaa
28		23	X		X			aaaa
29		24	Y		Y			aaaaa
30		25	Z		Z			aaaaaa
31		26	a		a
32		27	b		b		BASE64DECODE, does not decode properly, proper encoded base64
33		28	c		c
34		29	d		d		YQ=		=BASE64DECODE(H16)
35		30	e		e		YWE==		=BASE64DECODE(H17)
36		31	f		f		YWFh	aaa	=BASE64DECODE(H18)
37		32	g		g		YWFhYQ=		=BASE64DECODE(H19)
38		33	h		h		YWFhYWE==		=BASE64DECODE(H20)
39		34	i		i		YWFhYWFh	aaaaaa	=BASE64DECODE(H21)
40		35	j		j
41		36	k		k
42		37	l		l
43		38	m		m
44		39	n		n
45		40	o		o
46		41	p		p
47		42	q		q
48		43	r		r
49		44	s		s
50		45	t		t
51		46	u		u
52		47	v		v
53		48	w		w
54		49	x		x
55		50	y		y
56		51	z		z
57		52	0		0
58		53	1		1
59		54	2		2
60		55	3		3
61		56	4		4
62		57	5		5
63		58	6		6
64		59	7		7
65		60	8		8
66		61	9		9
67		62	+		+
68		63	/		/
69
Sheet1

Cell Formulas
Range	Formula
C2	C2	=FORMULATEXT(C5)
C3	C3	=FORMULATEXT(E5)
B5:B68	B5	=SEQUENCE(64)-1
C5:C68	C5	=LET(s,SEQUENCE(64),CHAR(IFS(s<27,s+64,s<53,s+70,s=63,43,s=64,47,1,s-5)))
E5:E68	E5	=LET(a,CHAR(SEQUENCE(26,,65)),VSTACK(a,LOWER(a),SEQUENCE(10)-1,"+","/"))
H7:H12	H7	=BASE64ENCODE(G7)
I7:I12,I34:I39	I7	=FORMULATEXT(H7)
K7,H24,L23,L21,L17,H15	K7	=FORMULATEXT(K8)
K8	K8	=BASE64ENCODE(G7:G12)
H16:H21	H16	=B64E(G7:G12)
L18	L18	=INDEX(sf,K18+1)
L22,L24	L22	=XMATCH(CODE(K22),CODE(sf))-1
H25:H30	H25	=B64D(H16#)
H34:H39	H34	=BASE64DECODE(H16)
Dynamic array formulas.

Book1.xlsx
	A	B	C	D
1		2D array text (notes about Base64 Characters link)
2		The Base64 Alphabet contains 64 basic ASCII characters which are used to encode data. Yeah, that’s right, 64 characters is enough to encode any data of any length. The only drawback is that the size of the result will increase to 33%. However, its benefits are much more important, at least because all these symbols are available in 7-bit and 8-bit character sets.	Characters of the Base64 alphabet can be grouped into four groups: Uppercase letters (indices 0-25): ABCDEFGHIJKLMNOPQRSTUVWXYZ, Lowercase letters (indices 26-51): abcdefghijklmnopqrstuvwxyz, Digits (indices 52-61): 0123456789, Special symbols (indices 62-63): +/
3		In addition to these characters, the equal sign (=) is used for padding. That is, the equal sign does not own an index and is not involved in the encoding of data. By and large, the padding character ensures that the length of Base64 value is a multiple of 4 bytes and it is always appended at the end of the output. Nevertheless, the heart of the algorithm contains only 64 characters, and for each of them there is a unique index. Only indices determine which characters will be used to encode the data, and only thanks to them you can “recover” the original data.	It is very important to note that the Base64 letters are case sensitive. This means that, for example, when decoding the values “QQ==”, “Qq==”, “qq==”, and “qQ==” four different results are obtained.
4
5		=B64E(B2:C3)
6		VGhlIEJhc2U2NCBBbHBoYWJldCBjb250YWlucyA2NCBiYXNpYyBBU0NJSSBjaGFyYWN0ZXJzIHdoaWNoIGFyZSB1c2VkIHRvIGVuY29kZSBkYXRhLiBZZWFoLCB0aGF0knMgcmlnaHQsIDY0IGNoYXJhY3RlcnMgaXMgZW5vdWdoIHRvIGVuY29kZSBhbnkgZGF0YSBvZiBhbnkgbGVuZ3RoLiBUaGUgb25seSBkcmF3YmFjayBpcyB0aGF0IHRoZSBzaXplIG9mIHRoZSByZXN1bHQgd2lsbCBpbmNyZWFzZSB0byAzMyUuIEhvd2V2ZXIsIGl0cyBiZW5lZml0cyBhcmUgbXVjaCBtb3JlIGltcG9ydGFudCwgYXQgbGVhc3QgYmVjYXVzZSBhbGwgdGhlc2Ugc3ltYm9scyBhcmUgYXZhaWxhYmxlIGluIDctYml0IGFuZCA4LWJpdCBjaGFyYWN0ZXIgc2V0cy4==	Q2hhcmFjdGVycyBvZiB0aGUgQmFzZTY0IGFscGhhYmV0IGNhbiBiZSBncm91cGVkIGludG8gZm91ciBncm91cHM6IFVwcGVyY2FzZSBsZXR0ZXJzIChpbmRpY2VzIDAtMjUpOiBBQkNERUZHSElKS0xNTk9QUVJTVFVWV1hZWiwgTG93ZXJjYXNlIGxldHRlcnMgKGluZGljZXMgMjYtNTEpOiBhYmNkZWZnaGlqa2xtbm9wcXJzdHV2d3h5eiwgRGlnaXRzIChpbmRpY2VzIDUyLTYxKTogMDEyMzQ1Njc4OSwgU3BlY2lhbCBzeW1ib2xzIChpbmRpY2VzIDYyLTYzKTogKy8==
7		SW4gYWRkaXRpb24gdG8gdGhlc2UgY2hhcmFjdGVycywgdGhlIGVxdWFsIHNpZ24gKD0pIGlzIHVzZWQgZm9yIHBhZGRpbmcuIFRoYXQgaXMsIHRoZSBlcXVhbCBzaWduIGRvZXMgbm90IG93biBhbiBpbmRleCBhbmQgaXMgbm90IGludm9sdmVkIGluIHRoZSBlbmNvZGluZyBvZiBkYXRhLiBCeSBhbmQgbGFyZ2UsIHRoZSBwYWRkaW5nIGNoYXJhY3RlciBlbnN1cmVzIHRoYXQgdGhlIGxlbmd0aCBvZiBCYXNlNjQgdmFsdWUgaXMgYSBtdWx0aXBsZSBvZiA0IGJ5dGVzIGFuZCBpdCBpcyBhbHdheXMgYXBwZW5kZWQgYXQgdGhlIGVuZCBvZiB0aGUgb3V0cHV0LiBOZXZlcnRoZWxlc3MsIHRoZSBoZWFydCBvZiB0aGUgYWxnb3JpdGhtIGNvbnRhaW5zIG9ubHkgNjQgY2hhcmFjdGVycywgYW5kIGZvciBlYWNoIG9mIHRoZW0gdGhlcmUgaXMgYSB1bmlxdWUgaW5kZXguIE9ubHkgaW5kaWNlcyBkZXRlcm1pbmUgd2hpY2ggY2hhcmFjdGVycyB3aWxsIGJlIHVzZWQgdG8gZW5jb2RlIHRoZSBkYXRhLCBhbmQgb25seSB0aGFua3MgdG8gdGhlbSB5b3UgY2FuIJNyZWNvdmVylCB0aGUgb3JpZ2luYWwgZGF0YS4==	SXQgaXMgdmVyeSBpbXBvcnRhbnQgdG8gbm90ZSB0aGF0IHRoZSBCYXNlNjQgbGV0dGVycyBhcmUgY2FzZSBzZW5zaXRpdmUuIFRoaXMgbWVhbnMgdGhhdCwgZm9yIGV4YW1wbGUsIHdoZW4gZGVjb2RpbmcgdGhlIHZhbHVlcyCTUVE9PZQsIJNRcT09lCwgk3FxPT2ULCBhbmQgk3FRPT2UIGZvdXIgZGlmZmVyZW50IHJlc3VsdHMgYXJlIG9idGFpbmVkLg=
8
9		=B64D(B6#)
10		The Base64 Alphabet contains 64 basic ASCII characters which are used to encode data. Yeah, that’s right, 64 characters is enough to encode any data of any length. The only drawback is that the size of the result will increase to 33%. However, its benefits are much more important, at least because all these symbols are available in 7-bit and 8-bit character sets.	Characters of the Base64 alphabet can be grouped into four groups: Uppercase letters (indices 0-25): ABCDEFGHIJKLMNOPQRSTUVWXYZ, Lowercase letters (indices 26-51): abcdefghijklmnopqrstuvwxyz, Digits (indices 52-61): 0123456789, Special symbols (indices 62-63): +/
11		In addition to these characters, the equal sign (=) is used for padding. That is, the equal sign does not own an index and is not involved in the encoding of data. By and large, the padding character ensures that the length of Base64 value is a multiple of 4 bytes and it is always appended at the end of the output. Nevertheless, the heart of the algorithm contains only 64 characters, and for each of them there is a unique index. Only indices determine which characters will be used to encode the data, and only thanks to them you can “recover” the original data.	It is very important to note that the Base64 letters are case sensitive. This means that, for example, when decoding the values “QQ==”, “Qq==”, “qq==”, and “qQ==” four different results are obtained.
12
13		check decode(encode(text))=text ( B64D(B64E(text))=text )
14		=EXACT(B2:C3,B10#)
15		TRUE	TRUE
16		TRUE	TRUE
17
Sheet2

Cell Formulas
Range	Formula
B5,B14,B9	B5	=FORMULATEXT(B6)
B6:C7	B6	=B64E(B2:C3)
B10:C11	B10	=B64D(B6#)
B15:C16	B15	=EXACT(B2:C3,B10#)
Dynamic array formulas.

 

[felixstraube]

Hi Xlambda. Thanks for taking the time and testing both functions.
You are absolutely right. I got the theory of base64 enconding from this page:
Sunshine2k's homepage - Understanding and implementing Base64

And when I got the basic part working I never read about the "=" sign padding. My mistake.

Nice work in making them take not only one cell but any array!!!

I'm pretty new to excel 365 and its functions so when I write a formula I use a lot of names in the LET function to make them more readable or understandable (at least they are for me ). That's why they are so long.

 

[felixstraube]

I was trying your functions but i get an error when using them: #NAME?.
Isn't there something missing? This part doesn't seem to give CONCAT any arguments:

0), CONCAT)) + 1)"

 

[felixstraube]

Here is the corrected formula. Now it adds the "=" at the end if needed. And I copied your idea of mapping a range rather than applying the formula to a single cell.

=LAMBDA(a, MAP(a,
LAMBDA(y, LET(
base64codes, {"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K";"L";"M";"N";"O";"P";"Q";"R";"S";"T";"U";"V";"W";"X";"Y";"Z";"a";"b";"c";"d";"e";"f";"g";"h";"i";"j";"k";"l";"m";"n";"o";"p";"q";"r";"s";"t";"u";"v";"w";"x";"y";"z";"0";"1";"2";"3";"4";"5";"6";"7";"8";"9";"+";"/"},
inputStr, y,
chars, MID(inputStr, SEQUENCE(, LEN(inputStr)), 1),
t, CONCAT(DEC2BIN(CODE(chars), 8)),
b, MID(t, SEQUENCE(, LEN(t)), 1),
r, WRAPROWS(b, 6, 0),
base64chars, BYROW(r, LAMBDA(x, CONCAT(x))),
outputStringLength, ROUNDUP(LEN(inputStr)/3,0)*4,
CONCAT(EXPAND(INDEX(base64codes, BIN2DEC(base64chars) + 1), outputStringLength,,"="))
))
))

 

[felixstraube]

Can a Moderator please edit the OP and correct the formula and add that the argument now can take a range of cells? Thanks

 

[felixstraube]

I read a little about why the padding with "=" is necessary. And the only explanation I found is if you are going to concatenate the encoded strings, like so:

Here we can see that if we concatenate the unpadded encoded strings and then want to decode it again we get a wrong result.
With the padding (i had to modify mi lambda formula again to make it work properly) we get exactly what is expected.
But... why bother to pad with another character like "="? Why not use the character "A" which in the Base64 symbol table is also 000000. Actually my formula does this. Substitutes "=" with "A" and decodes normally.
And in the test, where I convert all = in A and then decode again we get the same result.
So I really don't understand why "=" is used.