How to use CHAR and CODE functions (video)

Abstract

Each character you see displayed in Excel has a number. In this video we look at two functions that work with these numbers directly: CODE and CHAR.

Transcript

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

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

If I use CODE with a capital A ("A") it returns 65.

I'll get the same result if I supply a reference to B6, which also 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 B.

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 a 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 number for an uppercase "A," I get 97, which is the number for a lower case "a." The same goes for the letter "B."

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

This is useful when you need to format information with line breaks in a formula.

To illustrate, let's look at this list of names and addresses. If you wanted to create something like a mailing list, you could use CONCATENATION (see example).

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

Here's where the CHAR function is useful. To add line breaks, I can use the CHAR function with the appropriate number. Since I'm working on Windows, I'll use CHAR(10).

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