Quick, clean, and to the point

How to use CHAR and CODE functions

Each character you see displayed in Excel has a number. Excel has two functions that work with these numbers directly: CODE and CHAR.

Let's look first at the CODE function. The CODE function accepts one argument, which the text for which you want a numeric code.

CODE ("A") returns 65

I'll get the same result if I supply a reference to B6, which contains a capital A.

With a lowercase "a", CODE returns 97.

If I copy that formula down, you can see that CODE returns a number for each character, including punctuation and special symbols.

The CHAR function is basically the reverse of the CODE function. It accepts one argument -- a number between 1-255 -- and returns the corresponding character.

CHAR(65) returns an uppercase A.

So, copying the function down, we'll get the character we started with in column A.

Using CODE and CHAR, we can map the first 255 characters of any given font. I've got the numbers here already, and using a named range I defined earlier I can  select all empty cells and enter the CHAR function to get the character for each number.

And, if I change to another font, say Wingdings, we'll see the first 255 characters for that font.

One thing you might notice is that upper and lower case letters are exactly 32 numbers apart. If I add 32 to 65, the code for an uppercase A, I get 97, the code for a lower case a. Same for the letter B.

Finally, I want to point out two important characters. The character number 10 on Windows and number 13 on a Mac will cause a line break inside a cell.

This is useful when you need format information in a formula.

To illustrate, let's look at this list of names and addresses. If you wanted to create something like a mailing address, you could use concatenation, something like this:

However, notice that everything just ends up on the same line. And text wrapping doesn't help.

Here's where the character function is useful. To add line breaks, I can use the CHAR function with the appropriate number. Since I'm working on the [platform] platform, I need to use CHAR (10).

The result is a formatted address with line breaks in the correct place. Just make sure you have line wrapping turned on.


Related shortcuts

Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Charts video training course
Video training for Excel Tables
Dynamic Array Formulas