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, 4, 6-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.

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

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

• Now, go back to the Excel sheet and insert the formula into a selected cell where you want to decode your text.

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

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.

• Return to the Excel sheet now, and put the formula into the cell that contains the text you wish to decode.

• Further, hit the Enter key.

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

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.

• Further, go back to the worksheet and insert the following formula there.

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

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!

The post How to Decode Base64 in Excel (2 Suitable Examples) appeared first on ExcelDemy.