Excel's CHAR and CODE Functions

01
of 02

Excel CHAR/UNICHAR Function

Insert Special Characters and Symbols into Excel with the CHAR and UNICHAR Functions
Insert Characters and Symbols with the CHAR and UNICHAR Functions. © Ted French

Each character displayed in Excel is in actual fact a number.

Computers only work with numbers. Letters of the alphabet and other special characters - such as the ampersand "&" or hashtag "#"  - are stored and displayed by assigning a different number for each one.

Originally, not all computers use the same numbering system or code page when numbering the different characters.

For example, Microsoft developed code pages based on the ​ANSI code system - ANSI is short for American National Standards Institute - while Macintosh computers used the Macintosh character set.

Problems can arise when trying to convert the character codes from one system to another resulting in garbled data.

Universal Character Set

To correct this problem a universal character set known as Unicode system was developed during the late 1980's that gives all characters used in all computer systems a unique character code.

There are 255 different character codes or code points in the Windows ANSI code page while the Unicode system is designed to hold over one million code points.

For the sake of compatibility, the first 255 code points of the newer Unicode system match those of the ANSI system for western language characters and numbers.

For these standard characters, the codes are programmed into the computer so that typing a letter on the keyboard enters the code for the letter into the program in use.

Non-standard characters and symbols - such as the copyright symbol - © - or accented characters used in various languages can be entered into a program by typing in the ANSI code or Unicode number for the character in the desired location.

Excel CHAR and CODE Functions

Excel has a number of functions that work with these numbers directly: CHAR and CODE for all versions of Excel, plus UNICHAR and UNICODE introduced in Excel 2013.

The CHAR and UNICHAR functions return the character for a given code while the CODE and UNICODE functions do the opposite - give the code for a given character. For example, as shown in the image above,

  • the result for =CHAR (169) is the copyright symbol © ;
  • while the result for =CODE(©) is 169.

Similarly, if the two functions were nested together in the form of

=CODE(CHAR(169))

the output for the formula would be 169, since the two functions do the opposite job of the other.

The CHAR/UNICHAR Functions Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for the CHAR function is:

= CHAR (Number)

while the syntax for the UNICHAR function is:

= UNICHAR (Number)

Number - (required) a number between 1 and 255 specifying which character you want.

Notes:

The Number argument can be number entered directly into the function or a cell reference to the location of the number in a worksheet.

-If the Number argument is not an integer between 1 and 255, the CHAR function will return the #VALUE! error value as shown in row 4 in the image above

For code numbers greater than 255, use the UNICHAR function.

-if a Number argument of zero (0) is entered, the CHAR and UNICHAR functions will return the #VALUE! error value as shown in row 2 in the image above

Entering the CHAR/UNICHAR Function

Options for entering either function include typing the function in manually, such as:

=CHAR(65) or =UNICHAR(A7)

or using the functions' dialog box to enter the function and the Number argument.

The following steps were used to enter the CHAR function into cell B3 in the image above:

  1. Click on cell B3 to make it the active cell - the location where the results of the function are displayed
  2. Click on the Formulas tab of the ribbon menu
  3. Choose Text from the ribbon to open the function drop down list
  4. Click on CHAR in the list to bring up the function's dialog box
  5. In the dialog box, click on the Number line
  6. Click on cell A3 in the worksheet to enter that cell reference into the dialog box
  7. Click OK to complete the function and close the dialog box
  8. The exclamation mark character - ! - should appear in cell B3 since its ANSI character code is 33
  9. When you click on cell E2 the complete function =CHAR(A3) appears in the formula bar above the worksheet

CHAR/UNICHAR Function Uses

Uses for the CHAR/UNICHAR functions would be to translate code page numbers into characters for files created on other types of computers.

For example, the CHAR function is often used to remove unwanted characters that appear with imported data. The function can be used in conjunction with other Excel functions such as TRIM and SUBSTITUTE in formulas designed to remove these unwanted characters from a worksheet.

02
of 02

Excel CODE/UNICODE Function

Find Character Codes for Text and Symbols with the CODE and UNICODE Functions in Excel
Find Character Codes with the CODE and UNICODE Functions. © Ted French

The CODE/UNICODE Function Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for the CODE function is:

= CODE (Text)

while the syntax for the UNICODE function is:

= UNICODE (Text)

Text - (required) the character for which you want to find the ANSI code number.

Notes:

The Text argument can be a single character surrounded by double quotation marks (" ") entered directly into the function or a cell reference to the location of the character in a worksheet as shown in rows 4 and 9 in the image above

If the text argument is left empty the CODE function will return the #VALUE! error value as shown in row 2 in the image above.

The CODE function only displays the character code for a single character. If the text argument contains more than one character - such as the word Excel shown in rows 7 and 8 in the image above - only the code for the first character  is displayed. In this case it is the number 69 which is the character code for the uppercase letter E.

Uppercase vs. Lowercase Letters

Uppercase or capital letters on the keyboard have different character codes than the corresponding lowercase or small letters.

For example, the UNICODE/ANSI code number for the uppercase "A" is 65 while the lowercase "a" UNICODE/ANSI code number is 97 as shown in rows 4 and 5 in the image above.

Entering the CODE/UNICODE Function

Options for entering either function include typing the function in manually, such as:

=CODE(65) or =UNICODE(A6)

or using the functions' dialog box to enter the function and the Text argument.

The following steps were used to enter the CODE function into cell B3 in the image above:

  1. Click on cell B3 to make it the active cell - the location where the results of the function are displayed
  2. Click on the Formulas tab of the ribbon menu
  3. Choose Text from the ribbon to open the function drop down list
  4. Click on CODE in the list to bring up the function's dialog box
  5. In the dialog box, click on the Text line
  6. Click on cell A3 in the worksheet to enter that cell reference into the dialog box
  7. Click OK to complete the function and close the dialog box
  8. The number 64 should appear in cell B3 - this is the character code for the ampersand character " & "
  9. When you click on cell B3 the complete function =CODE (A3) appears in the formula bar above the worksheet
Format
mla apa chicago
Your Citation
French, Ted. "Excel's CHAR and CODE Functions." ThoughtCo, Jan. 7, 2018, thoughtco.com/excels-char-and-code-functions-3123796. French, Ted. (2018, January 7). Excel's CHAR and CODE Functions. Retrieved from https://www.thoughtco.com/excels-char-and-code-functions-3123796 French, Ted. "Excel's CHAR and CODE Functions." ThoughtCo. https://www.thoughtco.com/excels-char-and-code-functions-3123796 (accessed January 17, 2018).