CHAR function

The CHAR function in Excel is used to return the character associated with a given ASCII code (or Unicode code point in modern versions of Excel). This function can be useful when you need to insert special characters or control characters into your text.

Syntax

=CHAR(number)

Parameters

• number (required): The ASCII code (or Unicode code point) for which you want to return the corresponding character. This number must be between 1 and 255 for ASCII characters, or higher for Unicode characters (in modern Excel versions).

Key Points

• ASCII Code: The CHAR function is primarily used to return characters based on the ASCII code in the range of 1 to 255. For example, the ASCII code for the letter “A” is 65, so CHAR(65) would return “A”.
• Unicode: In modern versions of Excel (Excel 2013 and later), the CHAR function can handle Unicode characters as well. However, if you need to work with larger Unicode code points (beyond 255), you might prefer using the UNICHAR function, which supports a broader range of characters.
• Control Characters: Some ASCII codes correspond to control characters, such as newline or tab. For example, CHAR(10) returns a line break (newline), and CHAR(9) returns a tab character.

Example Usage

1. Basic Usage with ASCII Code

To get the character associated with ASCII code 65:

=CHAR(65)

This returns:

A

2. Using CHAR with a Control Character

You can also use CHAR for special characters such as line breaks or tabs. For example, to insert a line break (ASCII code 10), you would use:

=CHAR(10)

This would insert a line break in the cell. (Note: You may need to enable “wrap text” in the cell to see the line break correctly.)

3. Using CHAR for Other Special Characters

For example:

• CHAR(32) corresponds to a space character.
• CHAR(34) corresponds to the double quote (“) character.

=CHAR(32)   ' Returns a space
=CHAR(34)   ' Returns a double quote (")

4. Combining Characters with CHAR

You can combine characters using CHAR in formulas. For instance, to create a string with a newline:

="First Line" & CHAR(10) & "Second Line"

This will return:

First Line
Second Line

When displayed in a cell with “wrap text” enabled, it will show two lines of text.

5. Using CHAR with Numbers for Non-Printable Characters

Some ASCII codes correspond to non-printable characters that can be used for special formatting. For example:

=CHAR(9)  ' Returns a tab character
=CHAR(13) ' Returns a carriage return (used for line breaks in some systems)

Notes

• ASCII Code Limit: The CHAR function in Excel works with ASCII codes in the range of 1 to 255. For higher Unicode characters, use the UNICHAR function instead.
• Platform Dependence: The behavior of certain characters, especially control characters (like tab or newline), may vary depending on the platform and the application you’re using Excel in.
• Formatting: If you’re using special characters like newlines, remember that “Wrap Text” should be enabled in the cell formatting options to ensure the content displays as intended.

Related Functions

• UNICHAR: The UNICHAR function works similarly to CHAR, but it can handle Unicode code points beyond 255, allowing you to represent a much wider range of characters, including emoji and other special symbols.

  Example:

  =UNICHAR(128512)  ' Returns a smiley emoji (😀)
  

• CODE: The CODE function is the reverse of CHAR. It returns the ASCII code or Unicode code point of the first character in a given text string.

  Example:

  =CODE("A")   ' Returns 65
  

• TEXT: While not directly related, the TEXT function allows you to format numbers as text, which can be useful when dealing with characters and symbols in a formatted manner.

The CHAR function is a powerful tool for working with ASCII and Unicode characters in Excel, helping you insert special characters, control characters, or symbols into your data and reports.