30 Excel Functions in 30 Days: 08 – CHAR

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

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.

NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).

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

Char00

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

Char01a

After you create the formula, format the cell with Wrap Text, to see the line break, instead of that strange symbol between the strings.

Char01b

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.

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

Char02a

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)

Char02b

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)

Char03a

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.

Char03b

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.

Char04

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.

YouTube link: Get Characters With Excel CHAR Function

 

_____________

You may also like...

9 Responses

  1. Gregory says:

    Since I switch between Windows and Mac, the Character Reference Chart looks to be a great idea.

  2. Contextures Blog » 30 Excel Functions in 30 Days: Conclusion says:

    […] CHAR […]

  3. Contextures Blog » Add Line Break in Excel Formula says:

    […] add a line break between the text and the total amount, you can use the Excel CHAR function, with the number 10. Use the & operator to join the line break character to the other text in […]

  4. Art Babb says:

    This series is so great. Down-to-earth and valuable to the guy off the street. Thank you for the time and effort – never seen anything like this on the web anywhere else. Have a question on the comments. The post dated Feb 1 2011 at 12:05am. […] CHAR […] I don’t understand what is being said here. It follows a post that discusses Windows and Mac and a Character Ref Chart. The post […] CHAR […] doesn’t seem to answer or reply to the preceding post. Can you help me understand what the post is saying? I ask because there are similar posts in Days 1 > 6. Again, thank you for your site. Really well done.

  5. Art, thanks for your comment, and I’m glad that you’re finding the series to be helpful.

    Those strange comments are automatically created when another blog post links to this article.

    You can just ignore them.

  6. John says:

    Debra using CHAR you can return the column letter number and then use in an INDIRECT formula easily at least for the 1st 26 columns ie =CHAR(COLUMN()+64)
    above 26 columns I guess needs a more complex formula using IFs or CHOOSE

    This series has been a really really good for me albeit I am seeing it a year or so later. Thank you!

  7. Mercy says:

    hi Debra
    I am trying to insert a forced line break in a cell which uses the CONCATENATE formula.
    CHAR(10) is not working for me, probably because I’m on a Mac. Would you know the Mac equivalent of CHAR(10) for the Mac character set, or point me in the right direction?
    Thanks so much!

  8. Mercy says:

    It worked!! Thanks so much again Debra!!

Leave a Reply

Your email address will not be published. Required fields are marked *