How to Use the UNICODE Function in Excel
Unicode is a global encoding standard that assigns a unique code point to every character in various writing systems. In Excel and Google Sheets, this standard allows users to manipulate and display text in multiple languages and symbols through specific formulas and functions.
Inserting Unicode Characters
To insert a Unicode character in Excel or Google Sheets, utilize the CHAR function. The syntax for this function is:
=CHAR(number)
Here, “number” refers to the Unicode value of the desired character. For instance, to insert a heart symbol (❤), the formula would be:
=CHAR(10084)
Counting Unicode Characters
In situations where you need to count the number of Unicode characters within a cell, combine the LEN and SUBSTITUTE functions. The SUBSTITUTE function is used to eliminate all non-Unicode characters from a text string, thereby facilitating an accurate unicode character count. For example:
=LEN(cell)-LEN(SUBSTITUTE(cell,CHAR(1),""))
Replacing Unicode Characters
To exchange specific Unicode characters within a text string for other characters, employ the SUBSTITUTE function. For example, to replace all instances of the Greek letter Delta (Δ) with an uppercase “D”, use:
=SUBSTITUTE(cell,CHAR(916),"D")
Filtering Data with Unicode Characters
To filter a dataset based on specific Unicode characters in Google Sheets, the FILTER function proves useful. To display rows containing the checkmark symbol (✔️) in a specific column, apply the following formula:
=FILTER(A2:A10, SEARCH(CHAR(10004),A2:A10))
Converting Unicode to Plain Text
When converting Unicode text to plain text in Excel or Google Sheets, a combination of SUBSTITUTE and possibly CONCATENATE functions can be used. Here is a method to transform a cell with Unicode text into plain text:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(cell,CHAR(8234)," "),CHAR(8236)," "), CHAR(8206)," ")
Mastering these functions for working with Unicode characters in Excel and Google Sheets can significantly improve your capabilities in handling and analyzing diverse text data across different languages and symbol systems.
More information: https://support.microsoft.com/en-us/office/unicode-function-adb74aaa-a2a5-4dde-aff6-966e4e81f16f