BASE64ENCODE

=BASE64ENCODE(stringToEncode)
stringToEncode
required. The string that will be encoded with Base64
Encodes string with Base64 encoding.
F

felixstraube

Active Member
Joined
Nov 27, 2023
Messages
492
Office Version
  1. 365
Platform
  1. Windows
  2. Web
Based on Theory of this page:
Sunshine2k's homepage - Understanding and implementing Base64

BASE64ENCODE function encodes string with Base64 encoding.

Excel Formula: 
=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
AB
1Input StringLAMBDA Encode
2SunU3Vu
3Office Communications Server 2007T2ZmaWNlIENvbW11bmljYXRpb25zIFNlcnZlciAyMDA3
Sheet1
Cell Formulas
RangeFormula
B2:B3B2=BASE64ENCODE(A2)
Lambda Functions
NameFormula
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)) ))
 
Last edited by a moderator:
Upvote 0
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,
1705837182386.png

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:

Excel Formula: 
IFERROR(CHAR(charDec), "")
 
felixstraube said:
Hi Xlambda. Thanks for taking the time and testing both functions.
Click to expand...
Hi Felix, you are very welcome!
felixstraube said:
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.
Click to expand...
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.
felixstraube said:
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:
Excel Formula: 
0), CONCAT)) + 1)"
Click to expand...
Probably you did not define sf the base64 char set
New eta reduce lambdas does not need arguments like =BYROW(a,CONCAT)
felixstraube said:
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:
Click to expand...
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
Excel Formula: 
=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
Excel Formula: 
=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
Excel Formula: 
=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
ABCDEFGHIJKL
1eta lambda behavior
2anytime this also can be used
3=BYROW(B4:C5,CONCAT)=BYROW(B4:C5,LAMBDA(x,CONCAT(x)))
4121212
5343434
6
7=LET(fn,LAMBDA(x,PRODUCT(x)/SUM(x)),BYROW(B4:C5,fn))
80.666666667
91.714285714
10
11assigning "=" 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#)
16z==eg==z
17zz=eno=zz
18zzzenp6zzz
19zzzz==enp6eg==zzzz
20zzzzz=enp6eno=zzzzz
21zzzzzzenp6enp6zzzzzz
22zzzzzzz==enp6enp6eg==zzzzzzz
23zzzzzzzz=enp6enp6eno=zzzzzzzz
24zzzzzzzzzenp6enp6enp6zzzzzzzzz
25zzzzzzzzzz==enp6enp6enp6eg==zzzzzzzzzz
26zzzzzzzzzzz=enp6enp6enp6eno=zzzzzzzzzzz
27zzzzzzzzzzzzenp6enp6enp6enp6zzzzzzzzzzzz
28zzzzzzzzzzzzz==enp6enp6enp6enp6eg==zzzzzzzzzzzzz
29zzzzzzzzzzzzzz=enp6enp6enp6enp6eno=zzzzzzzzzzzzzz
30zzzzzzzzzzzzzzzenp6enp6enp6enp6enp6zzzzzzzzzzzzzzz
31
Sheet3
Cell Formulas
RangeFormula
E3,H3,F15:G15,E7E3=FORMULATEXT(E4)
E4:E5E4=BYROW(B4:C5,CONCAT)
H4:H5H4=BYROW(B4:C5,LAMBDA(x,CONCAT(x)))
E8:E9E8=LET(fn,LAMBDA(x,PRODUCT(x)/SUM(x)),BYROW(B4:C5,fn))
D13D13=FORMULATEXT(D16)
E14E14=FORMULATEXT(E16)
D16:D30D16=REPT("z",SEQUENCE(15))
E16:E30E16=REPT("=",2-MOD(LEN(D16#)-1,3))
F16:F30F16=B64E(D16#)
G16:G30G16=B64D(F16#)
Dynamic array formulas.
 
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:
1705878108685.png

I get the same #NAME? error as before.

Nice formulas!!!
 
You must log in or register to reply here.

Forum statistics

Threads
1,216,499
Messages
6,131,012
Members
449,613
Latest member
MedDash99

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top