This article explains the syntax and the use of the CHAR function in Microsoft Excel.
The CHAR function returns the character specified by a code number (from 1 to 255) based on the ASCII value. It is often used to translate code page numbers received from other types of computer files into characters or when specific characters that are difficult to enter either in a cell or in a formula.
ASCII stands for the American Standard Code for Information Interchange, which is a character-encoding scheme that represents text in computers, communication equipment, and other devices that use text. In Excel, ASCII codes refer to the values assigned from 1 to 255 characters.
Most often, the CHAR function is used as part of a formula with other functions in Excel. It is used to display characters which are almost impossible to enter using a standard keyboard. Such Characters can be easily returned with CHAR function. For example, using CHAR with the function like SUBSTITUTE, you can easily remove or clean special and non-printable characters from the text.
Window uses the ANSI character set, whereas Macintosh uses the Macintosh character set. This is why the result returned by the Excel CHAR function for specific number codes may be different on different operating systems.
For example, you can use =CHAR (153) to return a trademark to a cell on window and CHAR (170) on the Mac.
- number: A required number is an integer from 1 to 255 specifying which character you want based on the character set used by the computer.
Examples: – CHAR Function
Let’s take a few examples to understand the real-life use of CHAR function in different situations.
Get Copyright Symbol
Let’s say you want to get a copyright symbol before the year value. Copyright is the 169th computer’s character set and to display the same, you can enter 169 as number argument to CHAR function as follow.
As you have seen above, the function =CHAR(169) returns the 169th character set, i.e. copyright symbol that you can further merge it with any string value.
Tip: You can use either CONCATENATE function or ampersand ( & ) trick to merge two or more strings together.
Add a Line Break
One of most obvious use of CHAR function is to insert a line break using a formula. The following spreadsheet shows the CHAR function along with concatenation operator, ampersand (&) inserts a line break after every string.
=A2 & CHAR(10) & B2 & CHAR(10) & C2 & CHAR(10) & D2
Let’s understand how above formula works:
The above formula is merging portion of text given in cell A2, B2, C2 & D2 using the ampersand (&) which is alternative to CONCATENATE in Excel. To insert a line break in between each piece of text, the 10th ANSI character set returns using CHAR function. However, make sure, the text wrapping is enabled on cells that contain line breaks.
Tip: The character code for a line break varies on different operating systems. On window, the line break is associated with the character set number 10 and on Mac, it is 13.
Additional Notes: –
- Excel Online only supports characters 9, 10, 13, 32 and above.
- The function occurs #VALUE! error if either:
- The number argument is a non-numeric, or
- The supplied number is greater than 255 or less than 1.
- The first 31 characters are non-printable characters and cannot be displayed by Excel. You can clean them using CLEAN function and use CODE function to know their code value.
- To know the code value of any specific character, you can also refer to the ‘Character Map’ on your PC. You can open the Character Map by typing ‘CharMap’ in the run dialog box (Window key + R) or Cortana.
- The characters returned are based on the character set used by your computer:
CODE | UNICHAR | UNICODE