UNICHAR Function

What is the UNICHAR Function?

The UNICHAR function in Excel returns the corresponding character based on the ANSI (American National Standards Institute) standard to code numerical data. It is especially accommodating when working with Unicode characters.

Using Unicode, each character is assigned a unique numerical value called a code point. UNICHAR helps return the corresponding character for a given Unicode code point.

The ANSI character encoding standard is also known as Windows-1252. The ANSI standard was introduced due to the character limitations in the ASCII format. The ANSI format supports other languages and accented characters like the Euro symbol.

At the same time, the ASCII character encoding standard is older and is a subset of the ANSI standard. It supports only a limited amount of characters compared to the much larger ANSII format.

Microsoft created ANSI as a standard for character encoding, which helped improve the ASCII format. Now, you may say we are not IT people. What does all this mean? Below, we will see a table explaining the differences between the two.

Understanding the UNICHAR Function in Excel

The function is found under the Text functions library in the Formula tab. The UNICHAR function returns the character represented by the given Unicode value.

Below, we see the Unicode function, which is the inverse of UNICHAR. It returns the numerical code point and takes the character as input.

The syntax for the function in Excel is:

=UNICHAR(number)

Where,

Number: This argument just needs the number to display a corresponding character.

The UNICHAR function takes only one argument, which is the Unicode number (code point), and returns the corresponding character. For example, if you input 461, you will get a corresponding character Ǎ.

This changes with different numbers, as each number has a corresponding unique character. We will demonstrate below how the function works.

How to use the UNICHAR Function in Excel?

Let us take a look at the steps for using the function:

Step 1: The first step is selecting the cell where the result appears.

Step 2: We will input the formula and press SHIFT + F3 to open the Insert function dialog box.

Step 3: In the number argument, we will type 461.

Step 4: After clicking OK, we see the character Ǎ displayed in cell A1.

Alternatively, we will type in 0 and see that the #VALUE! Error is displayed as the number 0 has no corresponding character.

UNICHAR Function Examples

Coders and other IT professionals use the function to code different characters of languages. If a person knows the code point of each character, he can write a word or sentence.

Example 1

Below are different numerical codes that will find the corresponding character using the Unichar function. The numbers are sequential in the first two rows and are random in the last to show different symbols with different codes.

We type the Unichar function and select cell A1 in the argument. We then drag the formula down to copy it. We will analyze each table to see the different corresponding characters.

We see the characters are not shown properly for the first 10 numbers. The characters these numbers represent are currently not found in the Excel library.

The numbers 101 to 110 display English alphabets from e to n as corresponding characters by using the Unichar function. We must remember that the characters are case sensitive, i.e., the code point for “e” is 101 but for “E” is 69.

The third table has numbers at random; thus, we see different characters. The first number, 2345, has a Devanagari script character, and the number 1666 has an Arabic script character. We even see non-linguistic characters like the recycled character ♲ corresponding to 9842.

Example 2

While UNICHAR has more Unicode than Char, which has a limit of 255 code points, it does have a limit. Here, we will try to find the number of digits the function can handle. First, we will try to input 6 digits and check if there is a character representing it.

It is still under the allowable range, but the character is not in Excel’s library. Now, we will add one more digit. By adding a zero, the display is empty, meaning that seven-digit numbers are out of the allowable range of the function.

UNICHAR Function Uses

You may wonder, what could such a function be used in your work? It looks like it is only useful for IT professionals. However, we can use these functions in several other ways to help ease our tasks.

Below are examples of some of the different ways this function can be used to our advantage.

1. Displaying Special Characters

We all use Latin or Greek characters in our mathematical and scientific work. These characters are not found on the keyboard, and you may have to copy-paste them from the Internet.

Knowing the right numerical code point of the character we want, we can use the Unichar function to return it without any hassle. Below are some examples of common characters and their code points that you may find useful.

The numbers from 913 to 969 have Greek alphabets commonly used in Math and Science formulas. Our example shows a common character, Sigma (Σ), which has a code point of 931. There are other Greek letters in the example, too.

Currency code points start at 8352 and go up to 8399. The Franc, Pound Sterling, and Won characters correspond to numbers 8355, 8356, and 8361, respectively. Lastly, we see a loading symbol with a code point of 10227.

2. Using Arrows

Arrows and other markings highlight a particular point or thing in your worksheet. Knowing how to insert arrows in our Excel workbooks may be helpful.

We sometimes need arrows or other markings to make the audience focus on a particular worksheet region. We can use the UNICHAR function to create these arrows and use them in our data.

3. Using Check Symbol

The check symbol is a universal symbol of approval. The cross symbol (✖) is used universally to show disapproval. With the help of the Unichar function, we can use these symbols to make analyzing our data easier.

In our example, we have data on the total number of cars in a city. We will use the IF function to analyze which city has less than 2 million cars.

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

We see a checkmark or tick on cities with cars equal to or more than 2 million and a cross on cities with cars less than 2 million.

These are some ways we can use the UNICHAR function to our advantage in Excel.

What is UNICODE Function?

When typing the UNICHAR function, we see the Unicode function below it. You may wonder, what is this function? How is it different? Below, we will see the differences between these two functions.

Unicode is a text function in Excel that displays the Code point for the first character input in the argument. The function’s allowable range is more than 255 characters. This means that it is the exact inverse of the UNICHAR function. The function has the same syntax as the UNICHAR function.

=UNICODE(text)

Where,

Text: This argument just needs the character to display the corresponding code point.

With the help of examples, we'll explore how the Unicode function works opposite to the UNICHAR function but is still related.

In cell B3, we type the UNICHAR function and input the number 100 in the argument. According to ANSI format, the corresponding character is the English alphabet “d.”

Next, in cell C3, we type the Unicode function and input the letter “d” into the argument. The answer displayed is 100, the code point of the alphabet “d.” This way, we see that both functions are inversely related to each other.

We see another example below, where in cell B3, we input the number 500 to find the corresponding character, and in cell C3, we input the same character and get the number 500.

CHAR Vs. UNICHAR Function

Two functions in Excel return the ASCII character of a code point: Char and UNICHAR functions. But why are there two formulas if both functions return the same result?

This is because the Char function has a range of only 255 numbers. For numbers more than 255, we can use the UNICHAR function. The following table describes the differences in an easily understandable manner.

The Char function returns the corresponding character of the specified code point according to the ASCII format. The differences are:

Below, we will test these differences by inputting the number 679 in both functions. We see that the Char function shows a #VALUE! Error, whereas the UNICHAR function shows the character "ʧ".

We will also test if the result is the same if we input a number below 255, for example, number 79. We see that both the cells display the same character, O. This proves that both functions are the same, with only the range being an exception.

Free Resources

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

• UNICODE Function
• UPPER Function
• VALUE Function
• VAR.P Function
• VAR.S Function