MS Excel: CHAR function to convert numeric values into characters

The CHAR function in Excel is used to return a character based on the ASCII code (or Unicode code point). It allows users to convert numerical values into characters, which can be particularly useful when you need to work with symbols, special characters, or control characters in your data.

This function is often used in tasks involving text manipulation, generating special symbols, or even constructing complex strings of text programmatically in Excel.

Syntax

=CHAR(number)

Parameters:

• number: A required argument. It is the numeric value that represents the character’s ASCII code (or Unicode code point) in the character set used by Excel. The value should be between 1 and 255 for standard ASCII characters, and up to 65535 for Unicode characters.

The CHAR function works by taking a numeric value (which corresponds to a character in the ASCII or Unicode table) and returning the character that corresponds to that code. For example:

• The ASCII code 65 corresponds to the letter "A".

• The ASCII code 32 corresponds to a space character.

In Excel, the CHAR function maps the given number to a specific character in the character set.

The CHAR function only accepts integer values. If you provide a non-integer value, it will round the number to the nearest integer.

Example Use Cases

Example 1: Return the Letter for ASCII Code

=CHAR(65)

This formula will return "A", as ASCII code 65 represents the letter A.

Example 2: Return a Space Character

=CHAR(32)

This formula will return a space character, as ASCII code 32 corresponds to a space in the character set.

Example 3: Generating Multiple Characters

=CHAR(10)

The number 10 represents a line break (also known as a newline), so this formula will insert a line break (creating a new line) when used in text concatenation.

Example 4: Concatenating Special Characters

You can use the CHAR function to insert special characters into strings by combining them with other text:

=CHAR(10) & "New Line" & CHAR(10) & "Another Line"

This formula will insert two line breaks, creating a multiline text result:

New Line Another Line

Practical Applications

Use Case	Description
Inserting Line Breaks	Use CHAR(10) to add line breaks within a string, useful for multi-line text.
Special Characters	Use CHAR to insert symbols or special characters that aren't on the keyboard (e.g., CHAR(169) for the copyright symbol).
Dynamic Text Generation	Combine CHAR with other text functions like CONCATENATE, TEXT, or SUBSTITUTE to create dynamic strings.
Formatting Outputs	In reports or text outputs, CHAR can be used to add spaces or formatting symbols automatically.

Summary

Feature	Detail
Function Name	CHAR
Purpose	Converts ASCII code to corresponding character
ASCII Range	1 to 255 (for standard characters)
Use Case	Insert special characters, line breaks, and dynamic text creation

Limitations

• The CHAR function works within the standard ASCII range (1 to 255). For characters outside this range (e.g., Unicode characters greater than 255), you may need to use alternative functions like UNICODE.

• Non-integer values will be rounded automatically.

• Only works with the default character set used in Excel. If the system uses a different encoding, the results may vary.

Related Functions

Function	Purpose
UNICODE	Returns the Unicode code point of the first character in a string
TEXT	Formats numbers and dates as text
CONCATENATE	Joins multiple text strings into one

Final Thoughts

The CHAR function is a simple but powerful tool for converting numbers into characters in Excel. Whether you’re inserting special symbols, formatting text, or automating text generation, it can greatly enhance your ability to manipulate data in Excel.