The CODE function in Excel returns the numeric code of the first character in a text string. The numeric code is based on the character set used by your computer. For example, in the standard ASCII character set, the letter A has the code 65, and the letter a has the code 97.
The CODE function can be useful for various purposes, such as:
- Converting text to numbers for calculations or comparisons
- Encoding or decoding messages or passwords
- Finding the position of a character in the alphabet
- Generating random codes or passwords
Syntax and Arguments
The syntax of the CODE function is:
=CODE(text)
The text argument can be:
- A text string enclosed in quotation marks, such as “A” or “Hello”
- A cell reference that contains a text value, such as A1 or B2
- A formula that returns a text value, such as LEFT(A1,1) or CONCATENATE(B2,C2)
The text argument must not be empty, otherwise the CODE function will return a #VALUE! error.
Examples
To use the CODE function in Excel, follow these steps:
- Open a new or existing workbook in Excel
- In any cell, enter the text value that you want to get the numeric code of, or select a cell that contains a text value
- In another cell, enter the formula
=CODE(text), where text is the text value or the cell reference that you entered or selected in the previous step - Press Enter to get the result
For example, suppose you have the following data in cells A1 to A5:
| A |
|---|
| Hello |
| World |
| 123 |
| A |
| a |
To get the numeric code of the first character in each cell, you can enter the following formulas in cells B1 to B5:
| A | B |
|---|---|
| Hello | =CODE(A1) = 72 |
| World | =CODE(A2) = 87 |
| 123 | =CODE(A3) = 49 |
| A | =CODE(A4) = 65 |
| a | =CODE(A5) = 97 |
You can also use the CODE function with other text functions, such as RIGHT, MID, LEN, or CHAR. For example, to get the numeric code of the last character in cell A1, you can use the formula =CODE(RIGHT(A1,1)), which will return 111.
Scenario and Result
Suppose you want to create a simple encryption system that converts a text message into a series of numbers, using the CODE function and some arithmetic operations. For example, you want to encrypt the message “Hello World” into “77 101 108 108 111 32 87 111 114 108 100”.
To do this, you can follow these steps:
- In cell A1, enter the message that you want to encrypt, such as “Hello World”
- In cell B1, enter the formula
=LEN(A1), which will return the number of characters in the message, such as 11 - In cell C1, enter the formula
=CODE(LEFT(A1,1)), which will return the numeric code of the first character in the message, such as 72 - In cell D1, enter the formula
=C1+5, which will add 5 to the numeric code, resulting in 77 - In cell C2, enter the formula
=CODE(MID(A1,2,1)), which will return the numeric code of the second character in the message, such as 101 - In cell D2, enter the formula
=C2+5, which will add 5 to the numeric code, resulting in 106 - Repeat the same process for the rest of the characters in the message, until you reach the last character in cell C11 and D11
- In cell E1, enter the formula
=D1, which will copy the value from cell D1, such as 77 - In cell E2, enter the formula
=E1&" "&D2, which will concatenate the values from cells E1 and D2, separated by a space, resulting in “77 106” - Repeat the same process for the rest of the values in column D, until you reach cell E11, which will contain the encrypted message, such as “77 101 108 108 111 32 87 111 114 108 100”
The following table shows the complete encryption process:
| A | B | C | D | E |
|---|---|---|---|---|
| Hello World | 11 | 72 | 77 | 77 |
| 101 | 106 | 77 106 | ||
| 108 | 113 | 77 106 113 | ||
| 108 | 113 | 77 106 113 113 | ||
| 111 | 116 | 77 106 113 113 116 | ||
| 32 | 37 | 77 106 113 113 116 37 | ||
| 87 | 92 | 77 106 113 113 116 37 92 | ||
| 111 | 116 | 77 106 113 113 116 37 92 116 | ||
| 114 | 119 | 77 106 113 113 116 37 92 116 119 | ||
| 108 | 113 | 77 106 113 113 116 37 92 116 119 113 | ||
| 100 | 105 | 77 106 113 113 116 37 92 116 119 113 105 |
The result of the scenario is that the message “Hello World” is encrypted into “77 106 113 113 116 37 92 116 119 113 105”.
