Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Decode Base64 in Excel (2 Suitable Examples)

There is currently no native function in Excel or VBA for Base64 text encoding and decoding, despite this being a widespread requirement. This will guarantee that the data is transported without being altered in any way. In this article, we will demonstrate different examples to decode Base64 in Excel.


Download Practice Workbook

You can download the workbook and practice with them.


What Is Base64?

Binary data is translated into ASCII text format using the encoding and decoding method Base64. The exact 6 bits of data are constituted by  Base64 digits. Therefore, 46-bit digits may contain 3, 8-bit bytes (3*8=24). You may be pretty certain that the data will arrive on the other side of the wire uncorrupted since you can typically count on the same 64 characters being present in numerous character sets.

Binary data may be represented in an ASCII string using Base64. The act of converting a base64 representation of a string of strange-looking text back into the original binary or text data is known as “base64 decoding.”


2 Different Examples to Decode Base64 in Excel

A Roman script made up of letters, numbers, plus signs, and slashes may be created using the encoding technique Base64. It allows you to transform photos, emoticons, and even Chinese characters into “readable” text that may be stored or sent elsewhere. Let’s look at the examples to decode Base64 in Excel.


1. Apply Excel VBA Code for HTML Decode String

The ability to incorporate picture files or even other binary resources into text resources like HTML and CSS files makes Base64 especially popular on the World Wide Web.

STEPS:

  • Firstly, go to the Developer tab from the ribbon.
  • Secondly, click on Visual Basic from the Code category to open the Visual Basic Editor. Or press ALT+F11 to open the Visual Basic Editor.

excel base64 decode

  • This will appear in the Visual Basic Editor where we write our codes.
  • Thirdly, click on Module from the Insert drop-down menu bar.

excel base64 decode

  • This will create a Module in your workbook.
  • And, copy and paste the VBA code shown below.

VBA Code:

Public Function HtmlDecode(StringToDecode As Variant) As String
    Set HFile = CreateObject("htmlfile")
    Set a = HFile.createElement("T")
    a.innerHTML = StringToDecode
    HtmlDecode = a.innerText
End Function
  • Further, press CTRL+S to save the file.

excel base64 decode

  • Now, go back to the Excel sheet and insert the formula into a selected cell where you want to decode your text.
=HtmlDecode(B5)
  • Then, press the Enter key from your keyboard.

  • Drag the Fill Handle icon down to duplicate the formula over the range. Or, to AutoFill the range, double-click on the plus (+) symbol.

  • Finally, you will see the result.

Read More: How to Change Encoding in Excel (3 Suitable Methods)


2. Create User Defined Function in Excel to Decode Base64

Let’s see another example to decode Base64 in Excel VBA.

STEPS:

  • Likewise, in Example-1, go to Developer tab > Visual Basic > Insert  > Module.
  • After that, copy and paste the VBA code below.

VBA Code:

Function Decoding(b64$)
    Dim i
    With CreateObject("Microsoft.XMLDOM").createElement("b64")
        .DataType = "bin.base64": .Text = b64
        i = .nodeTypedValue
        With CreateObject("ADODB.Stream")
            .Open: .Type = 1: .Write i: .Position = 0: .Type = 2: .Charset = "utf-8"
            Decoding = .ReadText
            .Close
        End With
    End With
End Function
  • To save the file, hit CTRL+S.

excel base64 decode

  • Return to the Excel sheet now, and put the formula into the cell that contains the text you wish to decode.
=Decoding(B5)
  • Further, hit the Enter key.

NOTE: At the conclusion of a Base64 functional, the equals sign “=” denotes padding. All bits are typically encoded and the size in bytes is divided by three (bits are divisible by 24).
  • To copy the formula over the range, drag the Fill Handle symbol downward. Alternatively, you can double-click the addition (+) sign to AutoFill the range.

  • Lastly, you can view the outcome.

Read More: How to Apply UTF 8 Encoding on CSV File in Excel


How to Encode Base64 in Excel

Encoding is mainly data conversion into a format that we need for various information processing requirements. Let’s see how we can encode base64 in Excel.

STEPS:

  • To begin, click the Developer tab on the ribbon.
  • Second, launch the Visual Basic Editor by clicking on Visual Basic.
  • Alternatively, you may access the Visual Basic Editor by pressing ALT+F11.
  • Or, right-click on the sheet and choose View Code from the menu.
  • Next, pick the Module from the drop-down box under Insert.
  • And the visual basic window will appear.
  • Write the code there.

VBA Code:

Function Encoding(text$)
    Dim i
    With CreateObject("ADODB.Stream")
        .Open: .Type = 2: .Charset = "utf-8"
        .WriteText text: .Position = 0: .Type = 1: i = .Read
        With CreateObject("Microsoft.XMLDOM").createElement("b64")
            .DataType = "bin.base64": .nodeTypedValue = i
            Encoding = Replace(Mid(.text, 5), vbLf, "")
        End With
        .Close
    End With
End Function
  • Save the file by pressing CTRL+S on your keyboard.

excel base64 decode

  • Further, go back to the worksheet and insert the following formula there.
=Encoding(B5)
  • Consequently, press the Enter key.

  • After that, drag the Fill Handle icon to copy the formula over the range. Or, double-click on the plus (+) sign. This also duplicates the formula.
  • And that’s it! You will get your result.

Read More: How to Encode Survey Data in Excel (with Easy Steps)


Things to Remember

  • You should start by removing any padding characters from the encoded string’s end. After that, you convert each base64 character back to its 6-bit binary equivalent. In order to convert the data back to its original format, you must first divide the bits into byte-sized (8-bit) chunks.
  • While using VBA code make sure you save your file with the .xlsm extension.

Conclusion

The above examples will assist you to Decode Base64 in Excel. Hope this will help you! Please let us know in the comment section if you have any questions, suggestions, or feedback. Or you can glance at our other articles in the ExcelDemy.com blog!


Related Articles

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Team Leader. I'm a graduate in BSc in Computer Science and Engineering from United International University. I love working with computers and solving problems. I’ve always been interested in research and development. Here I post articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo