What does the CODE function do?
This function returns the ANSI value of the first character in the input string. Simply put - it converts a letter to a number (which corresponds to it in the current system encoding).
The ANSI character set is used by Windows to identify each keyboard character with a unique number.
The ANSI set contains 255 characters. The set depends on the current system language, so the same formulas may return different values on different computers.
CODE: result and input format
Similar to all text functions, the CODE function converts the input value to text. During this conversion, characters in numeric format cells may change. This particularly applies to dates, times, and percentage format.
CODE function syntax
=CODE(Cell_or_value)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Text | CODE | UNICODE | Note |
| 2 | A | 65=CODE(A2) | 65=UNICODE(A2) | uppercase |
| 3 | a | 97=CODE(A3) | 97=UNICODE(A3) | lowercase — another code |
| 4 | Apple | 65=CODE(A4) | 65=UNICODE(A4) | only the first character |
| 5 | 5 | 53=CODE(A5) | 53=UNICODE(A5) | digit |
| 6 | é | 233=CODE(A6) | 233=UNICODE(A6) | Windows-1252 |
| 7 | € | 128=CODE(A7) | 8364=UNICODE(A7) | 128 in 1252, 8364 in Unicode |
| 8 | Ω | 63=CODE(A8) | 937=UNICODE(A8) | not in 1252 → 63 (?) |
| 9 | (empty) | #VALUE!=CODE(A9) | #VALUE!=UNICODE(A9) | empty text |
CODE returns the code in the system code page (here Windows-1252, the English Windows default), so the same character can get a different number on another computer. UNICODE is the same everywhere: € is always 8364.
The function is case-sensitive - letters in different cases have different codes.
For characters not present in the current system character table, 63 is returned, which when reverse converted by the CHAR function will return a question mark.
If an empty cell or zero-length string is passed to the function, it returns a #VALUE! error.
Character codes and the system code page
Codes from 0 to 127 are the same everywhere: they are the basic ASCII characters, such as digits (48–57), uppercase Latin letters (65–90) and lowercase ones (97–122). Codes from 128 to 255 depend on the code page of the system. On Windows with English regional settings, it’s Windows-1252, where letters with diacritics live:
- CODE("é") returns 233, CODE("ñ") returns 241, CODE("ü") returns 252;
- CODE("€") returns 128, CODE("©") returns 169.
On a computer with a different code page, the same codes correspond to other characters, and for a character that is not in the code page at all, CODE returns 63, the code of the question mark. To get the real Unicode code of any character, regardless of the system, use the UNICODE function, the counterpart of UNICHAR.
CODE formula examples
One of the bright examples I considered in a separate article - How to create alphabet in Excel. The article explains how to add one to a letter to get the next one. Of course, we don't add to the letter itself, but to its code. In general, see the article.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Letter | Code | Next code | Next letter |
| 2 | A | 65=CODE(A2) | 66=B2+1 | B=CHAR(CODE(A2)+1) |
| 3 | b | 98=CODE(A3) | 99=B3+1 | c=CHAR(CODE(A3)+1) |
| 4 | Y | 89=CODE(A4) | 90=B4+1 | Z=CHAR(CODE(A4)+1) |
| 5 | z | 122=CODE(A5) | 123=B5+1 | {=CHAR(CODE(A5)+1) |
CODE gives the letter’s number, +1 the next one, and CHAR turns it back into a letter. After Z comes [ (91) and after z comes { (123), so check the end of the alphabet.
Like the article? Help its author! Buy !SEMTools, it has lots of useful instruments to process text data.