30 Excel Functions in 30 Days: 08 – CHAR

Yesterday, in the 30XL30D challenge, we found character code numbers with the CODE function, and used it to reveal hidden characters.

CHAR Function

For day 8 in the challenge, we’ll examine the CODE function’s evil twin — the CHAR function.

Well, maybe CHAR isn’t evil, but “benevolent twin” just doesn’t have the same CHARacter.

So, let’s take a look at the CHAR information and examples, and if you have other tips or examples, please share them in the comments.

Function 08: CHAR

The 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.)

How Could You Use CHAR?

The CHAR function can help enter special symbols or specific characters, such as:

• Add a line break in a formula
• Find a specific letter in the alphabet
• List all the letters of the alphabet
• Create a reference table of character codes

CHAR Syntax

The CHAR function has the following syntax:

• CHAR(number)
  • a number between 1 and 255, specifying the character you want returned. The character is from the character set used by your computer

CHAR Traps

Just like the CODE function, CHAR results could be different if you switch to a different operating system. The codes for the ASCII character set (codes 32 to 126) are consistent, and most can be found on your keyboard.

However, the characters for the higher numbers (129 to 254) may vary, as you can see in the comparison charts shown here:

Differences between ANSI, ISO-8859-1 and MacRoman character sets

Example 1: Add a Line Break

To enter a line break in a cell, you can press Alt + Enter. When combining text strings in a formula, you can use CHAR(10) — the line break character.

=C2 & CHAR(10) & C3

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 a Letter in the Alphabet

Quick! What’s the 19th letter of the alphabet? With the CHAR function, you can quickly figure that out, without singing the alphabet song in your head.

he 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.
=CODE(“A”) or =CODE(B1)

Then you can use the CHAR function to find any other letter in the alphabet, by adding 64 to the requested number.

=CHAR(B5+64)

Example 3: List the Entire Alphabet

With 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.

=CHAR(A2 + 64)

Create a Custom List

If you want to enter the alphabet with the AutoFill feature, you can import your alphabet list into the Custom Lists feature.

1. Copy the cells with the alphabet list, and paste in the row below, as Values
2. Select the cells with the alphabet values (not the row with the CHAR formulas)
3. Click the Office button on the Excel Ribbon, then click Excel Options (In Excel 2010, click Options)
4. In the list of Categories, click Popular (In Excel 2010, click Advanced)
5. Click the Edit Custom Lists button
6. Click the Import button, then click OK, and close Excel Options.
7. Type a letter on the worksheet, and use the Fill Handle to complete the series.

Example 4: Create a Character Reference Chart

You 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).

1. On a worksheet, type the numbers from 32 to 255
2. In the adjacent column, use the CHAR function to show the character for each code.

To see the characters for a different font, like Symbol or Wingdings, change the font in the CHAR function column.

Download the CHAR Function File

To see the formulas used in today’s examples, download the CHAR function sample workbook. The file is zipped, and is in Excel 2007 file format.

Watch the CHAR Video

To see a demonstration of the examples in the CHAR function sample workbook, you can watch this short Excel video tutorial.

_____________