BASE64DECODE

[felixstraube]

BASE64DECODE function Decodes Base64 encoded string.

=LAMBDA(x,
    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, x,
        chars, MID(inputStr, SEQUENCE(,LEN(inputStr)),1),
        t, CONCAT(DEC2BIN(BYCOL(chars, LAMBDA(x, MATCH(TRUE, EXACT(x, base64codes),0)-1)), 6)),
        b, MID(t, SEQUENCE(,LEN(t)),1),
        r, WRAPROWS(b, 8, 0), 
        charDec, BYROW(r, LAMBDA(x, BIN2DEC(CONCAT(x)))),
        CONCAT(IFERROR(CHAR(charDec),""))
    )
)

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

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

Lambda Functions
Name	Formula
BASE64DECODE	=LAMBDA(x, 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, x, chars, MID(inputStr, SEQUENCE(,LEN(inputStr)),1), t, CONCAT(DEC2BIN(BYCOL(chars, LAMBDA(x, MATCH(TRUE, EXACT(x, base64codes),0)-1)), 6)), b, MID(t, SEQUENCE(,LEN(t)),1), r, WRAPROWS(b, 8, 0), charDec, BYROW(r, LAMBDA(x, BIN2DEC(CONCAT(x)))), CONCAT(IFERROR(CHAR(charDec),"")) ))

 

[felixstraube]

The corrected function as @Xlambda suggested. Thanks again for taking the time to test the functions.

=LAMBDA(a, MAP(a,
LAMBDA(input, 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, SUBSTITUTE(input, "=", ""),
chars, MID(inputStr, SEQUENCE(, LEN(inputStr)), 1),
t, CONCAT(DEC2BIN(BYCOL(chars, LAMBDA(x, MATCH(TRUE, EXACT(x, base64codes), 0) - 1)), 6)),
b, MID(t, SEQUENCE(, LEN(t)), 1),
r, WRAPROWS(b, 8, 0),
charDec, BYROW(r, LAMBDA(x, BIN2DEC(CONCAT(x)))),
CONCAT(IFERROR(CHAR(charDec), ""))
))
))

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]

Here is the las correction. Now it decodes concatenated encoded strings correctly.

=LAMBDA(a, MAP(a,
LAMBDA(input, 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, SUBSTITUTE(input, "=", "A"),
chars, MID(inputStr, SEQUENCE(, LEN(inputStr)), 1),
t, CONCAT(DEC2BIN(BYCOL(chars, LAMBDA(x, MATCH(TRUE, EXACT(x, base64codes), 0) - 1)), 6)),
b, MID(t, SEQUENCE(, LEN(t)), 1),
r, WRAPROWS(b, 8, 0),
charDec, BYROW(r, LAMBDA(x, BIN2DEC(CONCAT(x)))),
CONCAT(IFERROR(CHAR(charDec), ""))
))
))