The Excel UNICHAR function is a Text formula, designed to return the Unicode character corresponding to any specified numeric code. This function simplifies the process of incorporating unique symbols in Excel, especially those not readily available on a standard keyboard. This guide will walk you through the steps to effectively use the UNICHAR function in Excel. Additionally, we'll provide valuable tips and strategies for error handling when working with symbols in Excel.
Supported versions
- All Excel versions
Excel UNICHAR Function Syntax
The UNICHAR function in Excel has a simple syntax used to retrieve Unicode characters based on their numeric code. Unicode is a universal character encoding standard, where each character or symbol is assigned a unique numeric identifier. Here's how the syntax of the UNICHAR function looks:
=UNICHAR(number)
number: This argument is a numeric value representing the Unicode code point of the character you want to return. It must be an integer between 1 and 1114111 (inclusive), covering the full range of Unicode characters.
Examples of UNICHAR Function
The UNICHAR function is very easy as it requires a single argument as an input. You only need to enter the numeric code for the corresponding Unicode character. For example, to return a capital letter "A", use 65 as the number argument.
Of course, you can retrieve characters up to 255 using the CHAR function as well. The UNICODE function is especially useful for inserting symbols in Excel from the ASCII character set. For example, symbols like Ω, √ or emoji, ?, ? can be added with the help of this function.
Insert Tick Mark (of Check Mark) in Excel
There are different styles of check symbols and each has its own Unicode number. Two common examples are:
Regular Tick Mark: Unicode Number is 10003
=UNICHAR(10003) will insert a ✓ (regular check mark).
Heavy Tick Mark: Unicode Number is 10004
=UNICHAR(10004) will insert a ✔ (bold or heavy check mark).
Remember, the appearance of the tick mark might slightly vary based on the font used in the Excel worksheet. If you're using a specific font or style in your worksheet, you may want to test these to see which check mark looks best with your chosen font.
Note that besides using the UNICHAR function, there are alternative methods to insert a check mark in Excel.
Insert Hash Symbol (or Number Sign) in Excel
To insert a hash symbol (#), also commonly known as the pound sign or number sign, using the UNICHAR function in Excel, you can use the Unicode code for the hash symbol. The Unicode code point for the hash symbol is 35.
=UNICHAR(35) will insert a # (hash symbol).
Insert Currency Symbols in Excel
Another common usage of Excel UNICHAR function is to insert currency symbols. Here's how you can insert some common currency symbols:
=UNICHAR(36) will insert Dollar symbol ($)
=UNICHAR(163) will insert Pound symbol (£)
=UNICHAR(8364) will insert Euro symbol (€)
=UNICHAR(165) will insert Yen symbol (€)
=UNICHAR(8377) will insert Indian Rupee symbol (₹)
=UNICHAR(8381) will insert Ruble symbol (₽)
=UNICHAR(20803) will insert Yuan symbol (元)
This method is very useful when you're working with financial data and need to display currency symbols dynamically based on certain conditions or data in your worksheet. You can also combine the UNICHAR function with other Excel functions (like IF, VLOOKUP, or CHOOSE) to insert different currency symbols based on the content of another cell or specific criteria.
To insert currency symbols dynamically in Excel using the UNICHAR function in combination with the IF or IFS function, you can set up a formula that changes the currency symbol based on a specific condition or value in another cell. Here are example formulas.
=IF(A1="USD", UNICHAR(36), IF(A1="EUR", UNICHAR(8364), IF(A1="GBP", UNICHAR(163), "Unknown Currency")))
=IFS(A1="USD", UNICHAR(36), A1="EUR", UNICHAR(8364), A1="GBP", UNICHAR(163))
To insert currency symbols in Excel using the UNICHAR function combined with the CHOOSE function, you can create a formula that selects the correct currency symbol based on an index number. Here is an example formula:
=CHOOSE(A1, UNICHAR(36), UNICHAR(8364), UNICHAR(163))
Finally, you can combine these functions with TEXT formula to create a custom format. This allows you to display the number with the desired currency symbol and control the number formatting (like decimal places, thousand separators, etc.). Here is an example formula:
=CHOOSE(A1, UNICHAR(36), UNICHAR(8364), UNICHAR(163)) & TEXT(10000, "#,##0.00")
This will place the selected currency symbol based on cell A1 before the number and format the number 10000 with a thousands separator and two decimal places.
Use UNICODE Function to Find the Numeric Code of a Symbol
The UNICHAR and UNICODE functions in Excel are complementary to each other, but they serve opposite purposes. The UNICODE function primarily serves to convert individual symbols in Excel, especially the first character in a text string, into their respective Unicode code points. This function is crucial for identifying the numeric code point of characters, a necessity when working with text involving various languages and scripts. It plays a significant role in character encoding, helping users in situations where the visual representation of a character is not immediately apparent.
Beyond character identification, the UNICODE formula is used in data analysis, processing, and handling technical applications involving multiple languages and character sets. It helps in character-level analysis, text data comparison, sorting, and in dealing with encoding issues in complex documents or databases. The UNICODE formula is also instrumental in internationalization and localization applications, as it supports a wide range of global characters, ensuring text data compatibility and effective manipulation across different systems and platforms.
Error Handling in Excel UNICHAR Function
#VALUE! Error: This error occurs when the argument provided to the UNICHAR function is non-numeric, decimal, or a negative number. Since UNICHAR formula requires a whole number representing a Unicode point, ensure that the input is an integer. Use functions like INT or ROUND to convert a decimal number to an integer, or validate inputs to ensure they are numeric.
#N/A! Error: This error is displayed when the number provided is outside the valid Unicode range, which is between 1 and 1114111, or an invalid character. Use an IF statement to check if the number is within the valid range before using it in the UNICHAR function.
Invalid or Unsupported Unicode Characters: In some cases, a valid Unicode number might correspond to a character that is not supported by your system's font or by Excel. This might not result in a formal error message, but the character won't be displayed correctly or display empty ( ). Test for known supported characters or use alternative methods or fonts that support a wider range of Unicode characters.
To prevent errors from disrupting your worksheet's functionality, you can use Excel IFERROR or IFNA functions. These functions allow you to provide an alternative result when an error is encountered. Wrap your UNICHAR function within an IFERROR or IFNA function, specifying an alternative action or value if an error occurs. For example: IFERROR(UNICHAR(number), "Error") will display "Error" if the UNICHAR function results in an error.
