Two functions in Google Sheets, UNICHAR and CHAR, have similar descriptions and appear to produce the same output. Both convert numbers to symbols. More specifically, they both return the Unicode character corresponding to a decimal code. Google’s documentation states that the UNICHAR function returns the “character for a Unicode number,” and the CHAR function gets the “character associated with number.” Is there a difference?
Let’s compare the results from the two and then discuss what we find. Make a copy of this template to follow along.
Contents
Different Decimal Codes Sizes
We’ll start by running both functions on the five codes shown in cells A2 through A6. These codes are progressively larger Unicode decimal values. We used this span of numbers to see if there is a difference over the spectrum of available characters.
As you can see in the image, the outputs in columns B and C are identical. For example, =CHAR(A2) and =UNICHAR(A2) both result in @.
So far, we haven’t found why these two identical functions both exist. Next, let’s look at the two functions in Excel.
Origin of the Two Functions in Excel
There must be something else going on here. Why would two functions be the same? Let’s look at Excel for the answer. Excel has been around much longer than Google Sheets. Excel was released before the internet and before Unicode. The character sets available to Excel were either ANSI for Windows or the Macintosh character set. Both of these character sets had only 256 characters.
The CHAR function handles 256 characters but nothing more. To address the thousands of characters in Unicode, instead of adapting the CHAR function, Microsoft introduced the UNICHAR function. Let’s compare how the CHAR and UNICHAR functions work in Excel, and we’ll see the difference.
In Excel, the two functions have the same output with the 254 and 255 as seen in cells B2 through C3. But, since there are only 256 characters in the ANSI set, and 0 is the first character, the characters beyond code 255 do not have an ANSI code. After 255, the CODE function in Excel returns #VALUE!, as seen in cells B4 through B5.
CHAR and UNICHAR in Google Sheets
Google Sheets has the same two functions but chooses to make them both Unicode compatible. Let’s see how they treat the same codes.
Conclusion
After comparing the behavior of the two functions in Google Sheets and understanding their history in Excel, the CHAR function exists for compatibility reasons in Google Sheets but has been extended to function with Unicode. Google decided it would be more valuable to have both functions recognize Unicode; therefore, they are the same.