Formulas > Text > CODE and CHAR

Excel CODE and CHAR Function Examples

How to use the Microsoft Excel CODE function and Excel CHAR function. Identify cell characters for troubleshooting. Use UNICODE and UNICHAR when needed. Watch short videos, see written steps and screen shots, download free Excel workbook

Video: CODE Function Examples

The CODE function returns a numeric code for the first character in a text string. For Windows, the returned code is from the ANSI character set, and for Macintosh, the code is from the Macintosh character set

Watch this short video for examples of how to use the CHAR function. There are written steps below the video.

How Could You Use CODE?

The CODE function is in the Text Function category of Excel functions.

You can use the CODE function can help unravel mysteries in your data, such as:

• What hidden character is at the end of imported text?
• How can I type a special symbol in a cell?

CODE Syntax

The CODE function has the following arguments in its syntax:

• CODE(text)
  • text argument is the text string from which you want the first character’s code

CODE Traps

Results could be different if you switch to a different operating system.

• The codes for the ASCII character set (codes 32 to 126) are consistent, and most can be found on your keyboard.
• However, the characters for the higher numbers (129 to 254) may vary, as you can see in the comparison charts shown here: Differences between ANSI, ISO-8859-1 and MacRoman character sets
• For example, the ANSI code 189 is ½ and for the Macintosh it is O

Example 1: Get Hidden Character’s Code

When you copy text from a website, it might include hidden characters. The CODE function can be used to identify what those hidden characters are

For example, there is a text string in cell B3, and only “test” is visible — 4 characters. In cell C3, the LEN function shows that there are 5 characters in cell B3.

To identify the last character’s code, you can use the RIGHT function, to return the text value of the last character. Then, use the CODE function to return the code for that character.

• =CODE(RIGHT(B3,1))

In cell D3, the RIGHT/CODE formula shows that the last character has the code 160, which is a non-breaking space used on websites.

Example 2: Find a Symbol’s Code

To insert special characters in an Excel worksheet, you can use the Symbol command on the Ribbon’s Insert tab.

For example, you can insert a degree symbol ° or a copyright symbol ©.

After you insert a symbol, you can determine its code, by using the CODE function

• =IF(C3=””,””,CODE(RIGHT(C3,1)))

Once you know the code, you can use the numeric keypad (not the regular numbers) to insert the symbol. The code for the copyright symbol is 169. Follow these steps to enter that symbol in a cell.

On a keyboard with a numeric keypad

1. On the keyboard, press the Alt key
2. On the numeric keypad, type the code as a 4-digit number (add leading zeros if necessary): 0169
3. Press Enter, to see the copyright symbol in the cell.

On a keyboard with no numeric keypad

On your laptop, you might need to press special keys to use the numeric keypad function. Check the owner’s manual, for directions. Here’s what works on my Dell laptop

1. Press the Fn key, and the F4 key, to enable NumLock
2. Locate the numeric keypad within the letters on the keyboard. On my keyboard, J=1, K=2, etc.
3. Press the Alt key, and the Fn key, and using the numeric keypad, type the code as a 4-digit number (add leading zeros if necessary): 0169
4. Press Enter, to see the copyright symbol in the cell.
5. When finished, press the Fn key, and the F4 key, to disable NumLock

Video: CHAR Function Examples

The CHAR function returns a specified character, for the number entered, based on the character set used by your computer. (For Windows, the ANSI character set, and for Macintosh, the Macintosh character set.)

Watch this short video for examples of how to use the CHAR function. There are written steps below the video

How Could You Use CHAR?

The CHAR function is another one of the Excel text functions, and is the inverse of the CODE function. When you give the CHAR function a number, it returns the corresponding character from the character set.

The CHAR function can help enter special symbols or specific characters, such as:

• Add a line break in a formula
• Find a specific letter in the alphabet
• List all the letters of the alphabet
• Create a reference table of character codes

CHAR Syntax

The CHAR function has the following arguments in its syntax:

• CHAR(number)
  • a number between 1 and 255, specifying the character you want returned. The character is from the character set used by your computer

CHAR Traps

Just like the CODE function, CHAR results could be different if you switch to a different operating system.

• The codes for the ASCII character set (codes 32 to 126) are consistent, and most can be found on your keyboard.
• However, the characters for the higher numbers (129 to 254) may vary, as you can see in the comparison charts shown here: Differences between ANSI, ISO-8859-1 and MacRoman character sets

Example 1: Add a Line Break

To manually enter a line break in a cell, you can press Alt + Enter, but that doesn't work when you're entering a formula.

When combining text strings in a formula, you can use CHAR(10) — the ASCII value for the line break character.

• =C2 & CHAR(10) & C3

After you create the formula, format the cell with Wrap Text, to see the line break, instead of that strange symbol between the strings.

Example 2: Find Letter in Alphabet

Can you name the 19th letter of the alphabet?

With the CHAR function, you can quickly figure that out, without counting on your fingers!

The first step is to find the CODE for the first letter of the alphabet — A. You can type A in a cell, and refer to it, or type A as a text string in the function.

• =CODE(“A”)

or

• =CODE(B1)

After that, you can use the CHAR function to find any other letter in the alphabet, by adding 64 to the requested number.

• =CHAR(B5+64)

The formula's return value in cell C5 is the uppercase letter A.

Example 3: List Entire Alphabet

With Excel’s AutoFill feature, you can type two numbers, and quickly create an entire series of numbers.

Unfortunately, that doesn’t work if you type a couple of letters. However, you can use the CHAR function to create a list with the entire alphabet

Use the same technique of adding 64 to the number, to get a letter of the alphabet.

• =CHAR(A2 + 64)

Create a Custom List

If you want to enter the alphabet with the AutoFill feature, you can import your alphabet list into the Custom Lists feature.

• Copy the cells with the alphabet list, and paste in the row below, as Values

• Select the cells with the alphabet values (not the row with the CHAR formulas)

• Click the Office button on the Excel Ribbon, then click Excel Options (In Excel 2010, click Options)

• In the list of Categories, click Popular (In Excel 2010, click Advanced)

• Click the Edit Custom Lists button

• Click the Import button, then click OK, and close Excel Options.

• Type a letter on the worksheet, and use the Fill Handle to complete the series

Example 4: Create Character Reference Chart

You can create a reference chart with all the printable characters, by using the CHAR function. This chart will be a quick guide if you want to enter a symbol by typing (press Alt, and type the 4-digit code on the numeric keypad).

1. On a worksheet, type the numbers from 32 to 255
2. In the adjacent column, use the CHAR function to show the character for each code.

To see the characters for a different font, like Symbol or Wingdings, change the font in the CHAR function column.

Troubleshoot with CODE and CHAR

If you copy data from a web page, it might contain special characters, such as non-breaking spaces.

On your worksheet, those special characters don't stand out, but they can cause problems. For example, if you try to use that data for a lookup, with VLOOKUP, or with INDEX and MATCH, you'll get an error result in your formula.

Identify Problem Characters with CODE

In the screen shot below, there is a MATCH formula in cell B8, and its result is an #N/A error.

• =MATCH(A8,$A$4:$A$6,0)

There is a difference between the "Sweater Vest" in the lookup table (cell A5), and the "Sweater Vest" is in cell A8, which was copied from a web page.

To identify the difference, I used the CODE function in cells D5 and D8, to identify the 8th character's code number.

• In row 5, the code is 32 - a normal space character
• In row 8, the code is 160 - a non-breaking space character

Fix Problem Characters with CHAR

If possible, replace the non-breaking space characters with normal space characters, and the MATCH formula should work correctly.

However, in some situations, you might be able to change all of the data that contains non-breaking spaces. For example, if you copy and paste new data every day, that would add an extra step to your process.

• Macro: If you freqently need to replace non-breaking spaces with normal space characters, there is a macro that you can use. The VBA code is on the Fix Numbers that Don't Add Up page, in the Fix Hidden Characters section.

So, if you can't fix the web data, you can modify the MATCH formula instead, to prevent errors.

In the revised formula shown below, I added the SUBSTITUTE function to replace the non-breaking space, CHAR(160), with a normal space character, CHAR(32).

• =MATCH(SUBSTITUTE(A8,CHAR(160),CHAR(32)),$A$4:$A$6,0)

CODE and CHAR Limitations

In some cases, your data might have hidden characters, copied from a website, and the Excel CODE function doesn't recognize those characters.

This is usually caused by a limitation on the CODE and CHAR functions:

• CODE and CHAR use the basic ANSI character set in Windows
• ANSI character set has a maximum code number of 255.
• The hidden characters are probably from a different character set, and have a code number greater than 255
• CODE and CHAR functions treat those unknown characters as a question mark (code 63)

UNICODE Function and UNICHAR Function

To overcome this 255 code number limitation, you can use the following two functions, which were introduced in Excel 2013.

• UNICODE
• UNICHAR

For example, in the screen shot above,

• CODE function in cell C3 treats unknown hidden character as a question mark, and returns number 63, which is incorrect
• UNICODE function in cell D3 recognizes the hidden character in cell B3, and returns its Unicode number, 8237.

And in the screen shot below:

• CHAR function in cell C9 returns an error, because the its code number limit is 255.
• UNICHAR function in cell D9 returns the correct character, an uppercase sigma (Σ)

Download Sample File

To see the CODE and CHAR function examples from this page, download the CODE and CHAR function sample workbook. The zipped file is in xlsx format, and does not contain any macros.

____________

More Function Tutorials

Combine Text/Numbers

TEXT function

ADDRESS Function

INDEX / MATCH Functions

FORMULATEXT

Compare Lookup Functions

VLOOKUP