Excel UNICODE Function – How To Use

The UNICODE function is a type of text function in Excel that returns the code point, also known as Unicode number, against a character, text, number, or symbol.

Each letter, number, character, and symbol is assigned a unique numeric value under the Unicode universal encoding standard, which is also used as a computing standard.

The UNICODE function only returns the code against the first character of the string. Read further to find out several ways to use the UNICODE function.

Excel UNICODE Function

Syntax

The syntax of UNICODE function is as follows:

=UNICODE(text)

Arguments:

'text' – s the input argument against which the Unicode number (numeric code point) will be returned. The text is a compulsory argument which can be a text, character, or string in double quotes. It can also be a cell reference containing the input character/string or any function that returns a text value.

Important Characteristics of the UNICODE function

  • If the input argument text is left empty, the UNICODE function returns a #VALUE error.
  • The characters or text strings entered in the UNICODE function (text argument) must be enclosed in double quotes; else, the function gives a #NAME? error. Numbers and cell references can be used without double quotes.
  • The UNICODE function is an extension of the CODE function, so the first 128 characters of the Unicode and ANSII codes are the same.
  • The first 32 characters of the UNICODE function are non-printable characters.
  • Even though the UNICODE function is a text/string function, the input argument is in text format, whereas the return value is numeric.
  • Unlike the CODE function, the return value of the UNICODE function does not vary with the operating system; hence it is a universal character code.

Examples of UNICODE Function

Using the UNICODE function, we can add special characters, symbols, and even emoticons in Excel. There are other ways to insert symbols in Excel, such as copy-pasting it from the web or directly using the Insert > Symbol option. But right now we're exploring what we can get the UNICODE function to do.

You can quickly note down the Unicode of the most used symbols, such as a few emojis, arrows, bars for pictographs, other language alphabets, mathematical symbols, etc., for references.

The formula mentioned here will return the codes for all the symbols in column B, such as alpha, pi, tick mark, thumbs up, smile emoticon, and more.

=UNICODE(B3)

Examples of UNICODE Function

Example 1 – Unicode of Roman Numerals

As one of the characteristics of the UNICODE function is that it only applies to the first character of the string, we can try it out in a sentence. In this example, we have the Olympics' timeline from the beginning.

Unicode of Roman Numerals

Let's check what happens when we use the UNICODE function on the string in column B. Using the formula as follows:

=UNICODE(B3)

Unicode of Roman Numerals

The UNICODE function returns the Unicode value of the Roman numerals used at the beginning of the sentence.

Now you know the Unicode of Roman numerals too. Bookmark it to use it in the future instead of capital I and V.

Example 2 – World Currency using UNICODE Function

Living in a borderless world in the era of globalization, we often need to use different currencies while using Excel. Using the CODE and CHAR function, we can only insert currency symbols of Dollar, Cent, Pound, and Yen.

But, how to add the currency symbol of Euro, Indian Rupee, or Riyal? The UNICODE function can help us with the code number of all the currencies, and we can easily insert them using the UNICHAR function.

The formula will be as follows:

=UNICODE(C3)

World Currency using UNICODE Function

Example 3 – Remove Emoticons / Emojis from String

We live in a world where the use of emoticons has increased drastically in conversations. We recently downloaded a set of messages where a few of them included basic smiley emoticons, and we needed to clean them.

Remove Emoticons / Emojis from String

Doing it manually would take time and is not the most efficient way to remove the emojis from the text. The CLEAN function will only clean non-printable characters (first 32 characters of the Unicode); therefore, it will not be helpful here.

Instead, we can use the UNICODE and SUBSTITUTE functions. As observed, the emoticons used are basic smileys ranging from 128512 to 128580 in the Unicode character set and are used at the end of the sentence.

We can use the RIGHT function to extract the emoticon, then check if the Unicode of the extracted character falls within the given range. Firstly, the RIGHT function targets the first character of B3 from the right:

=RIGHT(B3)

We will use the IF function to check if the extracted character is a smiley emoticon. The IF logic that we are going to use is as follows.

If Unicode of the last character is between 128512 and 128580, then SUBSTITUTE it with an empty string; otherwise, print the sentence as it is.

In Excel, the complete formula can be used as follows.

=IF(UNICODE(RIGHT(B3))>=128512,IF(UNICODE(RIGHT(B3)<=128580),SUBSTITUTE(B3, RIGHT(B3),"")),B3)

Remove Emoticons / Emojis from String

Using the above formula, we could quickly clean all the emoticons. The last example in cell B9 had no emoticon; therefore, the function returns the sentence in its original form.

Example 4 – Finding Code Points for an Entire String

We know that by default UNICODE function will only get us the code point corresponding to the first character in the string. Now, let's try and take things one step further and try to find Unicode numbers for all the characters in a string.

For the sake of our example let's try to retrieve the codes for all the characters in the string "ExcelTrick". To accomplish this, we can use the UNICODE function with MID, LEN, and SEQUENCE functions.

The SEQUENCE function returns a sequential number series. For e.g.: SEQUENCE(5) returns 1,2,3,4,5. Using the LEN function, we can determine the length of the string. Then, supply the return value of the LEN function to the SEQUENCE function.

=SEQUENCE(LEN(B3)

This will give us a sequential series of numbers starting from 1 till the total length of the string.

Now, we will use the MID function to extract each character from the string and return the Unicode of each character in the string. The MID function extracts a given number of characters from the middle of the input text.

So, as per our requirement, the first argument of the MID function will be B3, the string. The second argument, which is the starting point of the extraction, can be the SEQUENCE and LEN function as we wish to extract the first character and then the second character and so on. The last argument is 1 as we only wish to extract one character at a time.

The final formula will look like this.

=MID(B3,SEQUENCE(LEN(B3)),1)

Finding Code Points for an Entire String

We have arrived at the individual characters of the string. Now to arrive at the code point of each character, compiling the above logic in an Excel formula, we will finally wrap it all in the UNICODE function as follows:

=UNICODE(MID(B3,SEQUENCE(LEN(B3)),1))

Finding Code Points for an Entire String

You finally have the Unicode code points of all the characters of the string. You can use the same logic to decipher any string or text.

UNICODE vs CODE Function

The CODE function returns the numeric value associated with a character in the ANSI code. The ANSI stands for American National Standards Institute, which encodes certain numbers, alphabets, and characters in a standard manner.

As the CODE function predates the UNICODE function, it only accepts characters with ANSI codes 1 – 255. For characters and symbols beyond 255, we must use the UNICODE function.

For codes of symbols like currency, Greek symbols, arrows, mathematical symbols, emoticons, bars for graphs, subscripts, superscripts, etc., we have to use the UNICODE function.

The first 128 characters of the CODE and UNICODE functions correspond to the same symbols.

UNICODE vs UNICHAR Function

The UNICHAR function is the opposite of the UNICODE Function. After several examples, we hope that you have understood the UNICODE function comprehensively. Like the UNICODE function returns the Unicode number against a character, the UNICHAR function returns the character or symbol against the Unicode number.

All the characteristics of the UNICODE and the UNICHAR function are similar. Let's understand it using an example.

UNICODE vs UNICHAR Function

The UNICODE of the watch emoticon is 8986, as seen using the UNICODE function in the example below. If we wish to insert the watch emoji on our Excel, we will UNICHAR(8986).

That's a wrap for the UNICODE function. While you practice these formulas and work up an appetite for some more functions and formulas, we'll have another one of these functions loaded for you.

Compiled by - Content Studio

Thanks for reading. If you have found this article helpful show your love by sharing it with your friends & colleagues. All the tutorials on the Excel Trick are produced, reviewed, and fact-checked by a team of experts. You can check out our team here.