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:
The CODE function has the following arguments in its syntax:
Results could be different if you switch to a different operating system.
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.
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
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
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
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:
The CHAR function has the following arguments in its syntax:
Just like the CODE function, CHAR results could be different if you switch to a different operating system.
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.
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.
After that, you can use the CHAR function to find any other letter in the alphabet, by adding 64 to the requested number.
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.
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.
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).
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.
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.
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.
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).
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:
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.
For example, in the screen shot above,
And in the screen shot below:
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.
Last updated: November 30, 2022 2:35 PM