Two functions in Google Sheets, UNICODE and CODE, appear to be the same. Both convert text to numbers. More specifically, they both return the Unicode decimal number of the first character they are given. Google’s documentation states that the UNICODE function returns the decimal Unicode value, and the CODE function returns the numeric Unicode map value. 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
Testing the Two Functions
Different Decimal Codes Sizes
We’ll start by running both functions on the five characters shown in cells A2 through A6. These characters represent 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, =CODE(A2) and =UNICODE(A2) both result in 64.
Next, let’s try text strings with multiple characters.
Strings With Multiple Characters
The first example only has one-character strings. Perhaps UNICODE and CODE will treat longer strings differently. Let’s try a few.
When testing with longer strings, the two functions still create the same output. The formula =CODE(A2) AND =UNICODE(A2) are both only evaluating the “H” in the string “Hello” and therefore are both returning the Unicode decimal code of 72.
Similarly, they evaluate only the first character of ↞↙ and return code 8606 for the ↞ symbol.
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 CODE function handles 256 characters but nothing more. To address the thousands of characters in Unicode, instead of adapting the CODE function, Microsoft introduced the UNICODE function. Let’s compare how the CODE and UNICODE functions work in Excel, and we’ll see the difference.
In Excel, the two functions have the same output with the “þ” and “ÿ” characters 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 63, as seen in cells B4 through B5.
CODE and UNICODE 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, it appears the CODE function exists for compatibility reasons in Google Sheets but has been extended to function with Unicode. As there is little to gain by limiting a function and producing endless 63s for modern characters, Google decided it would be more valuable to have both functions recognize Unicode.
Although the functions are in the same in Google Sheets, since the UNICODE function operates the same in Excel and is newer, choosing UNICODE over CODE is a good choice.
