You have come to the right place if you’re looking for instructions on how to encode data in Excel. Despite this being a necessity, there is currently no encoding or decoding function in Excel or VBA. This article will demonstrate how to encode data in Excel.
How to Encode Data in Excel: Step-by-Step Procedures
In this article, we will demonstrate how to encode data in Excel by employing VBA code with some easy steps. So, to know how to encode data, you can follow the below steps accordingly.
Step 1: Making Data Set with Proper Parameters
In this section, we will create a data set with some names, such as a Text column and an empty column named Encode.
- So, you can see our data set in the below image.
Step 2: Using the Developer Tab
The Excel built-in Developer tab offers the tools required to use Visual Basic for Applications (VBA) to execute a macro. As a convention, the tab is disabled. To make it visible on the toolbar at the top of the Excel window, it has to first be activated in the Options section of the Menu bar.
- We will open the Developer tab.
- Then, select the Visual Basic command.
Step 3: Creating VBA User-Defined Function
Here, we will create a new module to write the VBA code by opening the Visual Basic Applications window.
- The Visual Basic window will open.
- From the Insert option, we will choose the new Module to write the VBA code.
- Now, paste the following VBA code into the Module.
- Then, save the following 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
Read More: How to Decode Base64 in Excel
Step 4: Encoding Data in Excel
VBA is a programming language that may be used for a variety of tasks, and different types of users can use it for those tasks. Using the Alt + F11 keyboard shortcut, you can launch the VBA editor. In the last section, we will generate VBA code that makes it very easy to encode data in Excel.
- Go to the worksheet and apply the following formula in cell B5.
=Encoding(B5)- Then, click Enter.
- You will see the encoded result for the first person.
- Use the Fill Handle tool and drag it down from cell B5 cell to cell B11.
- Finally, you will get all the results in the below image.
Read More: How to Apply UTF 8 Encoding on CSV File in Excel
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice it by yourself.
Conclusion
In this article, we’ve covered step-by-step procedures to encode data in Excel. We sincerely hope you enjoyed and learned a lot from this article. Additionally, if you want to read more articles on Excel, you may visit our website. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.
