How to use CHAR and CODE functions

Abstract

Each character you see displayed in Excel has a number.

Excel has two functions that work with these numbers directly:CODEandCHAR(Character).

Let’s look first at theCODE 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.

With a lowercase “a” CODE returns 97.

TheCHAR functionis basically the reverse of the CODE function.

It accepts one argumenta number between 1-255and 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.

One thing you might notice is that upper and lower case letters are exactly 32 numbers apart.

The same goes for the letter “B.”

This is useful when you’re gonna wanna 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 useCONCATENATION(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 check that that you have “line wrapping” turned on.