How to use the UNICODE function
What is the UNICODE function?
Use the UNICODE function to return a Unicode number based on the first character of a given text string.
Table of Contents
1. Introduction
What is UNICODE?
Unicode provides a universal character set and encoding schemes to represent text consistently across computing technologies and interfaces. It supports internationalization and localization needs which enables global websites and apps to display text and accept input in any language script like Arabic, Chinese, Hindi etc. without restrictions.
- Provides a unique number for every character, regardless of the platform, device, application, or language.
- With the capacity to represent over 1.1 million characters, Unicode encompasses all the world's languages and writing systems in one unified encoding standard.
- Allows data exchange and processing of text in any language combination.
- Maintained by the Unicode Consortium and coordinated with ISO standards.
- Common Unicode encodings are UTF-8, UTF-16, and UTF-32, which provide variable length or fixed width representations.
What is a character set?
A character set is a defined collection of textual characters used to represent writing systems, symbols, and numbers. They provide a mapping between numeric codes and human-readable characters. Common character sets include ASCII, Unicode, UTF-8, Latin-1, etc.
What is the difference between the UNICODE function and the CODE function?
The UNICODE function returns the Unicode code point value of the first character in a text string, the CODE function returns the ANSI code value. UNICODE is more versatile for different languages and characters, CODE is limited to basic ANSI.
What is the difference between UNICODE and ANSI?
ANSI uses 8-bit encoding, unicode uses variable width encoding of 8-bit to 32-bit. ANSI is limited to English, unicode enables internationalization and localization. ANSI predates Unicode and is a legacy standard, Unicode is a newer standard.
2. Syntax
UNICHAR(text)
| text | Required. The character for which you want the corresponding Unicode number. |
3. Example 1
The UNICODE function takes the first character in a given text string and converts it to a number representing that character's code point in the Unicode system.
Formula in cell C3:
UNICODE("A") takes the first character "A" and converts it to its Unicode code point which is the number 65.
Formula in cell C4:
UNICODE("Z") takes character "Z" and converts it to its Unicode code point which is the number 90.
Formula in cell C5:
UNICODE("a") takes character "a" and converts it to its Unicode code point which is the number 97.
Formula in cell C6:
UNICODE("z") takes character "z" and converts it to its Unicode code point which is the number 122.
Formula in cell C7:
UNICODE("1") takes character "1" and converts it to its Unicode code point which is the number 49.
Formula in cell C8:
UNICODE("9") takes character "9" and converts it to its Unicode code point which is the number 57.
Formula in cell C9:
UNICODE("0") takes character "0" and converts it to its Unicode code point which is the number 48.
Formula in cell C10:
UNICODE("-") takes character "-" and converts it to its Unicode code point which is the number 45.
Formula in cell C11:
UNICODE("!") takes character "!" and converts it to its Unicode code point which is the number 33.
Formula in cell C12:
UNICODE("?") takes character "?" and converts it to its Unicode code point which is the number 63.
Formula in cell C13:
UNICODE("^") takes character "^" and converts it to its Unicode code point which is the number 94.
Formula in cell C14:
UNICODE("/") takes character "/" and converts it to its Unicode code point which is the number 47.
Formula in cell C15:
UNICODE("μ") takes character "μ" (Greek lowercase mu) and converts it to its Unicode code point which is the number 181.
How to convert the Unicode to hexadecimal?
The UNICODE function returns a number in the base 10 numeral system. Use the DEC2HEX function to convert the number to hexadecimal.
Explaining formula
Step 1 - Convert character to unicode
UNICODE(B3)
becomes
UNICODE("A")
and returns 65.
Step 2 - Convert decimal to hexadecimal
The DEC2HEX function converts a decimal number to a hexadecimal number.
Function syntax: DEC2HEX(number, [places])
DEC2HEX(UNICODE(B3),4)
becomes
DEC2HEX(65,4)
and returns 0041 in hexadecimal.
Step 3 - Concatenate strings
The ampersand character lets you concatenate strings in an Excel formula.
"U+"&"0041"
and returns "U+0041".
What is unicode in hexadecimal?
The formal Unicode notation to represent code points uses the format:
U+XXXX
Where:
U+ - Indicates a Unicode code point value
XXXX - Is the code point specified as a 4-digit hexadecimal number
Some examples:
U+0041 - Code point for capital letter A
4. Example 2
The image above shows a spreadsheet containing a string in cell B3 which is "A!d" without double quotes. The formula in cell D3 extracts each character and returns the corresponding unicode value separated by a comma.
Formula in cell D3:
The formula in cell D3 returns 65, 33, and 100 which corresponds to A, !, and d. Here is a breakdown of the formula:
- LEN(B3): Returns the number of characters in cell B3, in this example, 3.
- SEQUENCE(LEN(B3)): creates an array containing a sequence from 1 to n. n is 3 in this case.
- MID(B3,SEQUENCE(LEN(B3)),1) : Extract each character in cell B3. {"A"; "!"; "d"}.
- UNICODE(MID(B3,SEQUENCE(LEN(B3)),1)) : Convert each character to unicode. {65, 33, 100}
- TEXTJOIN(", ",TRUE,UNICODE(MID(B3,SEQUENCE(LEN(B3)),1))): Join characters. "65, 33, 100"
5. Example 3
This example demonstrates how to extract upper case letter from a cell value. The image above displays value 0AvDeRTqm4S in cell B3. The formula in cell D3 extracts each uppercase letter from 0AvDeRTqm4S.
Formula in cell D3:
The formula in cell D3 returns ADRTS which are all upper case letters in cell B3 which contains 0AvDeRTqm4S.
Use the following formula to extract all lowercase letters:
The formula above returns veqm which are all lower case letters in value 0AvDeRTqm4S.
6. Function not working
The UNICODE function returns:
- #VALUE! error value if data type is not valid.
- #VALUE! if the unicode numbers is outside the allowed range.
Useful links
Functions in 'Text' category
The UNICODE function function is one of 29 functions in the 'Text' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form