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.
If I use CODE with a capital 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 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 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 list, 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 Windows, 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.