UNICODE Function

What is the UNICODE Function?

The UNICODE function in Excel returns the code point of the referenced character according to the ANSI (American National Standards Institute) standard. The function is useful in coding a set of data into numerical code points.

A Unicode character is basically a character from any language or script, including special characters and symbols like $, !, Ǎ, etc.

The ANSI standard was introduced to further expand on the older and inefficient ASCII standard of character encoding. The standard is like a rule on encoding characters by giving them a unique numerical code that computers understand.

This way, people and organizations can seamlessly transfer data in different languages without translating them, as computers can do it with the help of the Unicode standard. 

The encoding also helps bypass different file requirements as all the systems adhere to the Unicode format. As a result, text data encoded in Unicode can be read by different operating systems, such as Windows and MacOS.

In Excel, this function displays the code point of different characters and can be used to know the code of different characters or to encode our text data in Excel.

UNICODE Function Formula

In Excel, the function is found under the Text functions library in the Formula tab. The formula describes that it returns the number corresponding to the first character of the text. 

Below we see this function, which is the inverse of Unichar, as it returns the numerical code point and takes the character as input.

The syntax for the function is

=UNICODE(text)

Where, 

Text: This is a required argument that needs the character and will return the code point of the first character in a text.

This function is simple as it only needs one argument, which is the text argument. For example, if we input the character “k” in the argument, the function returns the number 107 as the corresponding code point. Below we will demonstrate how to use the function in Excel.

UNICODE Function Steps

Let's take a look at some of the steps of how to use the function below:

Step 1

The first step is to select the cell we want the code to be displayed in.

Step 2

We will then input the formula and press SHIFT + F3 to open the Insert function dialog box. Here, we have used the Insert function dialog box for our understanding. We can directly input the character in the text argument in the cell.

Step 3

In the text argument, we will type in character k (Quotation marks are not needed here).

Step 4

After clicking OK, we see the number 107 displayed in cell A1.

How to use the UNICODE Function in Excel?

You may think that the Unicode function is only useful for IT professionals and not for common people. You are wrong. We can use the code points as a reference to which character to insert when transferring a file between different file extensions or systems.

Here, we will see some examples of the function and see what the numerical codes of often-used characters are.

Let’s take a look at the first example.

Example 1

Each English letter has a corresponding code point in Unicode. By knowing the code point of each alphabet, you can code a text file.

We have a table of all 26 letters of the English alphabet below. We will type in the Unicode function in cell B2 to find out the numerical code of the first alphabet, “A.” We will select cell A2 in the text argument and hit enter. The number 65 will be displayed.

To copy the formula, we will drag cell B2 down to cell B10. The formula automatically returns the code for corresponding cells.

We see that the code points are sequential from 65 to 90 for each alphabet. But what if the alphabet is in lowercase? We will check this in the next example.

Let’s take a look at the second example.

Example 2

We saw above the code points for Upper case alphabets, but are the code points for Lower case alphabets different? To answer this, we will do a similar example but with lowercase alphabets.

We see that the code points are different, and the code for the alphabet “a” is now 97 instead of 65. The only similarity we see is that even here, the codes are sequential and start from 97 up to 122.

Now let’s take another example where we check the codes for numbers, not letters.

Example 3

We know that the function requires a text or a character, and a number is displayed corresponding to the said character according to the function. We will test what would happen if we input a number in the text argument.

Here, we see that the number 0 has a Unicode code number “48”. In the first table, the numbers are also treated as characters and have a separate code for them. But in the second table, we see that the two-digit numbers show the same code number, “49”.

This is because the function only recognizes the first character in the text. In number 10, the function only recognizes number 1 and displays the code point “49” corresponding to it while ignoring the second digit.

Example 4

We know that if we input a character in the text argument without quotation marks, the function returns a #NAME? Error.

We will demonstrate another limitation of the function, which is that it will only recognize the first character in a text. In our example, we will input the word “CODE’ in the text argument and check what the function returns.

Here, we see that the code for the alphabet “C” is 67. When we apply the function on the word CODE the code shown is 67, the same as the letter “C”. This proves that the function will only recognize the first character in a text and return the code corresponding to it.

Practical Uses of UNICODE Function

In our day-to-day computer lives, we can utilize UNICODE in numerous ways. Most computers and files communicate using the ANSI format, which Unicode is based on.

Excel uses the ANSI codes to understand data and transfer data with other files as well. Below, we will see the different uses of the function.

1. Converting Different Scripts Into Code

There are hundreds of languages and different scripts that people use. To make sure that the characters of these different languages are displayed correctly across other systems and files correctly, it is important that the characters are coded using the function. 

Excel does this automatically when importing and exporting data.

2. Sort and Filter Data

We can also use the function to sort and filter data. Even though the function is not meant for this, it can be used in a way to sort and filter data. We will see this with the help of an example.

In our example, we will analyze how many cars are there in major cities around the world. 

There is also a condition that we have set to analyze our data. The condition is how many cities have cars more than, equal to, and less than 2 million. Each criterion has a symbol with which we can easily identify it.

If the number of cars exceeds 2,000,000, then we want the ✔ symbol. If it is equal to 2,000,000, then we want the ✦ symbol. If it is below 2,000,000, then we want the ✖ symbol.

The formula with these conditions is as follows:

=IF(C3>2000000,UNICHAR(10004),IF(C3=2000000,UNICHAR(10022),IF(C3<2000000,UNICHAR(10006))))

We insert the formula in cell D3 and drag it down to simultaneously apply the formula to corresponding cells. The formula will automatically display the referred symbol according to the criteria.

Now, if we want to sort the data, we can select the cell D2 to D6 and click on the sort from A to Z. A pop-up asking to either Expand the selection or continue with the current selection will be seen. Select the first option and click on Sort.

The data will get sorted in this fashion. We see that the sorting is in ascending order according to the Unicode of each character.

However, we want London to appear in the second row on the table as it is the city with the second highest number of vehicles. To do this, we will need to select Custom Sort, and in the Sort pop-up box, select Unicode below Column criteria and select Custom List under Order criteria.

The Custom List pop-up box will be seen, we will create a New List by entering the sequence we want under List Entries. We will enter the sequence 10004, 10022, 10006 and click Add.

The list will now be seen in the Custom Lists panel. Click OK on both the pop-up boxes to continue.

We will see the new custom list applied and see the new sorting below. You may say if we sort the Cars column in the ascending format, the same result will be achieved. But what if the number of cars was not mentioned? Then such sorting will come into use.

What is The UNICHAR Function?

The UNICHAR function is another function in Excel with the UNI Suffix. You may have seen it when typing the Unicode function and wondered what the use of this is. And is it related to the function?

Well, yes, both functions are related as the Unichar function is the inverse of the former one. 

The Unichar function is a Text function in Excel that displays the character referenced by the given numerical value. The function has one argument similar to the Unicode function, and the syntax is also the same.

=UNICHAR(number)

Where,

Number: is the argument that needs the number to display a corresponding character.

The argument required is a number instead of a text, as it will display the character referenced by the number. 

We will look at examples below that show how both functions are inversely related. In cell B3, we type the Unicode function to find the code point of the alphabet “z”.

The code point of “z” is 122. We will type this number in our Unichar function and see that the character displayed is “z”, thus proving that the functions are inversely related.

We see another example using both the functions in Row 4 where the character for code point 124 is a standing line (“|”).

CODE Function vs. UNICODE Function

The CODE function is a similar function that returns the code of the character under the ANSI standard of encoding. Now, you may think, if both functions return the same answer, then why two different functions?

This is because the function has one key difference, the former supports only 255 characters, and the latter supports the full range of Unicode characters.

Below, we will test these differences by inputting the character “Ǜ” in both functions. We see that the Code function shows an incorrect answer of 63, whereas the Unicode function shows the correct numerical code 475 for the character “Ǜ”.

We will also test if the result is the same when we input a character with a numerical code below 255, for example, number 63. We are going to input the “?” character, which corresponds with the number 63. This proves that both functions are the same, with only the range being an exception.

Researched and authored by Mohammad Sharjeel Khan | Linkedin

Reviewed and edited by Wissam El Maouch | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful  WSO resources:

• CONCATENATE Function
• DEGREES Function
• ISLOGICAL Function
• POISSON.DIST Function
• XIRR Function