The Unicode Standard is supported in Excel. However, some functions for text manipulation still support only ASCII or ANSI character sets. Examples of those include the CLEAN function and the TRIM function. The CODE function and the CHAR function used for character manipulation also fall into that category.
Full character manipulation is available through the UNICODE function and the UNICHAR function. As implemented in Excel, characters have their own code points, which correspond to numbers. Those numbers can be returned with the help of the UNICODE function, and related characters can be returned with the help of the UNICHAR function.
The UNICODE function returns the number corresponding to the first character of a given text.
The UNICODE function syntax is as follows:
= UNICODE ( text )
Text can be any Unicode character. If there is more than one character, only the first character will be converted. Spaces, line breaks, etc. also count as characters, as they also have their own Unicode code points.
All of the following ways of referencing text are valid:
These are the corresponding numbers of the selected currency symbols:
The UNICHAR function returns the Unicode character that is referenced by the given numeric value.
The UNICHAR function syntax is as follows:
= UNICHAR ( number )
A number can be any positive whole number in the available range supported by Excel. If a decimal number is entered, the rounded-down number result will be returned. If the number falls outside of the allowable range, there is no argument, or text is entered, an error will be returned.
Here are some examples of both correct and incorrect applications of the UNICHAR function:
The UNICHAR function returns can also be used in order to generate (a part of the) text strings in Excel.
In the following example, we are generating invoice reminders for our customers. With UNICHAR, we are returning both a space character and a currency symbol:
Dig deeper:
2 thoughts on “UNICODE and UNICHAR functions”