Formulas > Text > CODE and CHAR Excel CODE and CHAR Function ExamplesHow 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 ExamplesThe 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:
CODE SyntaxThe CODE function has the following arguments in its syntax:
CODE TrapsResults could be different if you switch to a different operating system.
|
Example 1: Get Hidden Character’s CodeWhen 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 CodeTo 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 keypadOn 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 ExamplesThe 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:
CHAR SyntaxThe CHAR function has the following arguments in its syntax:
CHAR TrapsJust like the CODE function, CHAR results could be different if you switch to a different operating system.
|
Example 1: Add a Line BreakTo 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 AlphabetCan 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.
or
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 AlphabetWith 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 ListIf 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 ChartYou 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 CHARIf 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 CODEIn 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.
|
CODE and CHAR LimitationsIn 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 FunctionTo 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:
|
Download Sample FileTo 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 |
Last updated: November 30, 2022 2:35 PM