The CHAR function in Excel returns a character based on a given numeric code. For example, =CHAR(65) returns the letter A, and =CHAR(169) returns the copyright symbol ©.
The CHAR function can be useful for inserting special characters that are not easily typed on the keyboard, such as symbols, emojis, or non-English letters. It can also be used to create custom formats, such as bullet points, separators, or borders.
How to Use the CHAR Function
The syntax of the CHAR function is:
=CHAR(number)
where number is a positive integer between 1 and 255 that corresponds to a character in the ASCII or ANSI character set. The character set depends on your system’s language settings.
To use the CHAR function, follow these steps:
- In a cell, type
=CHAR(and then enter the number of the character you want to insert. For example,=CHAR(100)will return the letter d. - Press Enter to complete the formula and display the result in the cell.
- You can copy and paste the formula to other cells, or use it in combination with other functions, such as CONCATENATE, REPT, or TEXT.
Example: Using the CHAR Function to Create a Custom Format
Suppose you have a table of sales data and you want to create a custom format that shows the percentage change from the previous month, with an up or down arrow indicating the direction of the change. You can use the CHAR function to insert the arrow symbols, and the TEXT function to format the numbers as percentages. Here is how you can do it:
| Month | Sales | Change |
|---|---|---|
| Jan | 12000 | – |
| Feb | 15000 | =TEXT((B3-B2)/B2,“+0.0%;-0.0%”)&CHAR(IF((B3-B2)/B2>0,129,130)) |
| Mar | 14000 | =TEXT((B4-B3)/B3,“+0.0%;-0.0%”)&CHAR(IF((B4-B3)/B3>0,129,130)) |
| Apr | 16000 | =TEXT((B5-B4)/B4,“+0.0%;-0.0%”)&CHAR(IF((B5-B4)/B4>0,129,130)) |
| May | 17000 | =TEXT((B6-B5)/B5,“+0.0%;-0.0%”)&CHAR(IF((B6-B5)/B5>0,129,130)) |
The result will look like this:
| Month | Sales | Change |
|---|---|---|
| Jan | 12000 | – |
| Feb | 15000 | +25.0%↗ |
| Mar | 14000 | -6.7%↘ |
| Apr | 16000 | +14.3%↗ |
| May | 17000 | +6.3%↗ |
Other Approaches to Insert Special Characters
Besides using the CHAR function, there are other ways to insert special characters in Excel, such as:
- Using the Insert Symbol dialog box. To access it, go to the Insert tab, click on Symbol, and choose the character you want from the list. You can also use the shortcut Alt+I, S.
- Using the Alt code. To use it, hold down the Alt key and type the numeric code of the character on the numeric keypad. For example, Alt+0169 will insert the © symbol. Note that the Alt code may vary depending on your system’s language settings.
- Using the Unicode code. To use it, type the hexadecimal code of the character preceded by a backslash and the letter U, and then press Alt+X. For example, \U00A9 will insert the © symbol. Note that this method only works in Excel 2019 and later versions.
