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
First time posting in the lambda forum. Hope I did it right. If not, let me know.
 
felixstraube said:
First time posting in the lambda forum. Hope I did it right. If not, let me know.
Click to expand...
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.
 
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
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)))
( 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
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("=", MOD(LEN(a), 3))
)
B64D(a) Base64 Decoding
a: any array 1D, 2D
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
ABCDEFGHIJKLMNOPQ
1defined name sf (sixty four) 2 ways, 2 formulas
2sf 1C5=LET(s,SEQUENCE(64),CHAR(IFS(s<27,s+64,s<53,s+70,s=63,43,s=64,47,1,s-5)))
32E5=LET(a,CHAR(SEQUENCE(26,,65)),VSTACK(a,LOWER(a),SEQUENCE(10)-1,"+","/"))
4
50AABASE64ENCODE, does not add "=" at the end where is needed, wrong resuls if input is an array
61BB
72CCaYQ=BASE64ENCODE(G7)=BASE64ENCODE(G7:G12)
83DDaaYWE=BASE64ENCODE(G8)YWFhYWFh
94EEaaaYWFh=BASE64ENCODE(G9)
105FFaaaaYWFhYQ=BASE64ENCODE(G10)
116GGaaaaaYWFhYWE=BASE64ENCODE(G11)
127HHaaaaaaYWFhYWFh=BASE64ENCODE(G12)
138II
149JJsingle cell
1510KK=B64E(G7:G12)Conversion dec -> 64 ; 64 -> dec
1611LLYQ=dec64
1712MMYWE===INDEX(sf,K18+1)
1813NNYWFh2C
1914OOYWFhYQ=
2015PPYWFhYWE==64dec
2116QQYWFhYWFh=XMATCH(CODE(K22),CODE(sf))-1
2217RRC2
2318SSsingle cell=XMATCH(CODE(K24),CODE(sf))-1
2419TT=B64D(H16#)c28
2520UUa
2621VVaadecoding is case sensitive
2722WWaaa
2823XXaaaa
2924YYaaaaa
3025ZZaaaaaa
3126aa
3227bbBASE64DECODE, does not decode properly, proper encoded base64
3328cc
3429ddYQ= =BASE64DECODE(H16)
3530eeYWE== =BASE64DECODE(H17)
3631ffYWFhaaa=BASE64DECODE(H18)
3732ggYWFhYQ= =BASE64DECODE(H19)
3833hhYWFhYWE== =BASE64DECODE(H20)
3934iiYWFhYWFhaaaaaa=BASE64DECODE(H21)
4035jj
4136kk
4237ll
4338mm
4439nn
4540oo
4641pp
4742qq
4843rr
4944ss
5045tt
5146uu
5247vv
5348ww
5449xx
5550yy
5651zz
575200
585311
595422
605533
615644
625755
635866
645977
656088
666199
6762++
6863//
69
Sheet1
Cell Formulas
RangeFormula
C2C2=FORMULATEXT(C5)
C3C3=FORMULATEXT(E5)
B5:B68B5=SEQUENCE(64)-1
C5:C68C5=LET(s,SEQUENCE(64),CHAR(IFS(s<27,s+64,s<53,s+70,s=63,43,s=64,47,1,s-5)))
E5:E68E5=LET(a,CHAR(SEQUENCE(26,,65)),VSTACK(a,LOWER(a),SEQUENCE(10)-1,"+","/"))
H7:H12H7=BASE64ENCODE(G7)
I7:I12,I34:I39I7=FORMULATEXT(H7)
K7,H24,L23,L21,L17,H15K7=FORMULATEXT(K8)
K8K8=BASE64ENCODE(G7:G12)
H16:H21H16=B64E(G7:G12)
L18L18=INDEX(sf,K18+1)
L22,L24L22=XMATCH(CODE(K22),CODE(sf))-1
H25:H30H25=B64D(H16#)
H34:H39H34=BASE64DECODE(H16)
Dynamic array formulas.

Book1.xlsx
ABCD
12D array text (notes about Base64 Characters link)
2The 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): +/
3In 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)
6VGhlIEJhc2U2NCBBbHBoYWJldCBjb250YWlucyA2NCBiYXNpYyBBU0NJSSBjaGFyYWN0ZXJzIHdoaWNoIGFyZSB1c2VkIHRvIGVuY29kZSBkYXRhLiBZZWFoLCB0aGF0knMgcmlnaHQsIDY0IGNoYXJhY3RlcnMgaXMgZW5vdWdoIHRvIGVuY29kZSBhbnkgZGF0YSBvZiBhbnkgbGVuZ3RoLiBUaGUgb25seSBkcmF3YmFjayBpcyB0aGF0IHRoZSBzaXplIG9mIHRoZSByZXN1bHQgd2lsbCBpbmNyZWFzZSB0byAzMyUuIEhvd2V2ZXIsIGl0cyBiZW5lZml0cyBhcmUgbXVjaCBtb3JlIGltcG9ydGFudCwgYXQgbGVhc3QgYmVjYXVzZSBhbGwgdGhlc2Ugc3ltYm9scyBhcmUgYXZhaWxhYmxlIGluIDctYml0IGFuZCA4LWJpdCBjaGFyYWN0ZXIgc2V0cy4==Q2hhcmFjdGVycyBvZiB0aGUgQmFzZTY0IGFscGhhYmV0IGNhbiBiZSBncm91cGVkIGludG8gZm91ciBncm91cHM6IFVwcGVyY2FzZSBsZXR0ZXJzIChpbmRpY2VzIDAtMjUpOiBBQkNERUZHSElKS0xNTk9QUVJTVFVWV1hZWiwgTG93ZXJjYXNlIGxldHRlcnMgKGluZGljZXMgMjYtNTEpOiBhYmNkZWZnaGlqa2xtbm9wcXJzdHV2d3h5eiwgRGlnaXRzIChpbmRpY2VzIDUyLTYxKTogMDEyMzQ1Njc4OSwgU3BlY2lhbCBzeW1ib2xzIChpbmRpY2VzIDYyLTYzKTogKy8==
7SW4gYWRkaXRpb24gdG8gdGhlc2UgY2hhcmFjdGVycywgdGhlIGVxdWFsIHNpZ24gKD0pIGlzIHVzZWQgZm9yIHBhZGRpbmcuIFRoYXQgaXMsIHRoZSBlcXVhbCBzaWduIGRvZXMgbm90IG93biBhbiBpbmRleCBhbmQgaXMgbm90IGludm9sdmVkIGluIHRoZSBlbmNvZGluZyBvZiBkYXRhLiBCeSBhbmQgbGFyZ2UsIHRoZSBwYWRkaW5nIGNoYXJhY3RlciBlbnN1cmVzIHRoYXQgdGhlIGxlbmd0aCBvZiBCYXNlNjQgdmFsdWUgaXMgYSBtdWx0aXBsZSBvZiA0IGJ5dGVzIGFuZCBpdCBpcyBhbHdheXMgYXBwZW5kZWQgYXQgdGhlIGVuZCBvZiB0aGUgb3V0cHV0LiBOZXZlcnRoZWxlc3MsIHRoZSBoZWFydCBvZiB0aGUgYWxnb3JpdGhtIGNvbnRhaW5zIG9ubHkgNjQgY2hhcmFjdGVycywgYW5kIGZvciBlYWNoIG9mIHRoZW0gdGhlcmUgaXMgYSB1bmlxdWUgaW5kZXguIE9ubHkgaW5kaWNlcyBkZXRlcm1pbmUgd2hpY2ggY2hhcmFjdGVycyB3aWxsIGJlIHVzZWQgdG8gZW5jb2RlIHRoZSBkYXRhLCBhbmQgb25seSB0aGFua3MgdG8gdGhlbSB5b3UgY2FuIJNyZWNvdmVylCB0aGUgb3JpZ2luYWwgZGF0YS4==SXQgaXMgdmVyeSBpbXBvcnRhbnQgdG8gbm90ZSB0aGF0IHRoZSBCYXNlNjQgbGV0dGVycyBhcmUgY2FzZSBzZW5zaXRpdmUuIFRoaXMgbWVhbnMgdGhhdCwgZm9yIGV4YW1wbGUsIHdoZW4gZGVjb2RpbmcgdGhlIHZhbHVlcyCTUVE9PZQsIJNRcT09lCwgk3FxPT2ULCBhbmQgk3FRPT2UIGZvdXIgZGlmZmVyZW50IHJlc3VsdHMgYXJlIG9idGFpbmVkLg=
8
9=B64D(B6#)
10The 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): +/
11In 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
13check decode(encode(text))=text ( B64D(B64E(text))=text )
14=EXACT(B2:C3,B10#)
15TRUETRUE
16TRUETRUE
17
Sheet2
Cell Formulas
RangeFormula
B5,B14,B9B5=FORMULATEXT(B6)
B6:C7B6=B64E(B2:C3)
B10:C11B10=B64D(B6#)
B15:C16B15=EXACT(B2:C3,B10#)
Dynamic array formulas.
b64.png
 
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.
 
Last edited by a moderator:
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)"

1705792465637.png
 
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.

Excel Formula: 
=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,,"="))
))
))
 
Can a Moderator please edit the OP and correct the formula and add that the argument now can take a range of cells? Thanks
 
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:

1705811637765.png


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.
 
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