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]

One more thought.
It would make sense to pad with "=" if we would encode real binary data. That is if the byte 00000000 would have to be encoded and decoded. Then it would make sense to pad with "=" to discard this 0000000 bytes from the result that are not part of the decoded binary message.
But in excel where we are encoding a string and the character CHAR(0) does not exist,

we don't have to worry about this. Because in the decoding process the 00000000 bytes are discarded anyways with this part of the formula:

IFERROR(CHAR(charDec), "")

 

[Xlambda]

Hi Xlambda. Thanks for taking the time and testing both functions.

Hi Felix, you are very welcome!

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.

The fact that we can write a function as simple succesive nested functions of a single input value gives in my opinion better instant acurate insight to a new user about how simple and straight forward the function is, no variables whatsoever needed. There are so Very Few functions capable of that, so somehow should be "celebrated" and noted. 10 and 12 nested functions straight. It's some kind of a performance.

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

Probably you did not define sf the base64 char set
New eta reduce lambdas does not need arguments like =BYROW(a,CONCAT)

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:

Found a corect simple way to calculate the numbers of "=" based on initial array only, independent of encoding char lengths.
=REPT("=",2-MOD(LEN(a)-1,3))
You need to define all these 3 formulas (including sf as a defined name). Hope that helps !! Sorry that I do not have any variable name, long or short.
sf

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

B64E(a)
Base64 Encode function: a: any array

=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("=", 2 - MOD(LEN(a) - 1, 3))
)

B64D(a)
Base64 Decode function: a: any array

=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
1					eta lambda behavior
2								anytime this also can be used
3					=BYROW(B4:C5,CONCAT)			=BYROW(B4:C5,LAMBDA(x,CONCAT(x)))
4		1	2		12			12
5		3	4		34			34
6
7					=LET(fn,LAMBDA(x,PRODUCT(x)/SUM(x)),BYROW(B4:C5,fn))
8					0.666666667
9					1.714285714
10
11					assigning "=" based on initial array char lengths, no matter the encoded char length
12
13				=REPT("z",SEQUENCE(15))
14				↓↓↓↓	=REPT("=",2-MOD(LEN(D16#)-1,3))
15				↓↓↓↓	↓↓↓↓	=B64E(D16#)	=B64D(F16#)
16				z	==	eg==	z
17				zz	=	eno=	zz
18				zzz		enp6	zzz
19				zzzz	==	enp6eg==	zzzz
20				zzzzz	=	enp6eno=	zzzzz
21				zzzzzz		enp6enp6	zzzzzz
22				zzzzzzz	==	enp6enp6eg==	zzzzzzz
23				zzzzzzzz	=	enp6enp6eno=	zzzzzzzz
24				zzzzzzzzz		enp6enp6enp6	zzzzzzzzz
25				zzzzzzzzzz	==	enp6enp6enp6eg==	zzzzzzzzzz
26				zzzzzzzzzzz	=	enp6enp6enp6eno=	zzzzzzzzzzz
27				zzzzzzzzzzzz		enp6enp6enp6enp6	zzzzzzzzzzzz
28				zzzzzzzzzzzzz	==	enp6enp6enp6enp6eg==	zzzzzzzzzzzzz
29				zzzzzzzzzzzzzz	=	enp6enp6enp6enp6eno=	zzzzzzzzzzzzzz
30				zzzzzzzzzzzzzzz		enp6enp6enp6enp6enp6	zzzzzzzzzzzzzzz
31
Sheet3

Cell Formulas
Range	Formula
E3,H3,F15:G15,E7	E3	=FORMULATEXT(E4)
E4:E5	E4	=BYROW(B4:C5,CONCAT)
H4:H5	H4	=BYROW(B4:C5,LAMBDA(x,CONCAT(x)))
E8:E9	E8	=LET(fn,LAMBDA(x,PRODUCT(x)/SUM(x)),BYROW(B4:C5,fn))
D13	D13	=FORMULATEXT(D16)
E14	E14	=FORMULATEXT(E16)
D16:D30	D16	=REPT("z",SEQUENCE(15))
E16:E30	E16	=REPT("=",2-MOD(LEN(D16#)-1,3))
F16:F30	F16	=B64E(D16#)
G16:G30	G16	=B64D(F16#)
Dynamic array formulas.

 

[felixstraube]

Hi, and thanks again for taking the time to answer.
Yes I didn't define the sf name. Will try it when i'm on a desktop app. Currently i'm on the web app only.
I guess that's also why this doesn't work:

I get the same #NAME? error as before.

Nice formulas!!!