Excel CHAR function (a Text function) gives a specific character when a valid number is given as the input. Some characters are hard to find, you can easily insert these characters by using the CHAR function. Any number between 1 to 255 has a character assigned to it in your computer according to ASCII.
The American System Code for Information Interchange, or ASCII, is a character encoding standard used in digital communications. A unique integer number is assigned to each character that can be entered in the CHAR function. The character can be a number, alphabet, punctuation marks, special characters, or control characters. For Example, the ASCII code for [comma], is 044. The lowercase alphabets a-z have ASCII values ranging from 097 to 122.
In this article, you will also get some other examples that use the CHAR function with different character codes and fix issues while using this function.
Introduction to The CHAR Function
♦ Objective
The CHAR function returns the character specified by the code number from the character set for your computer.
♦ Syntax
CHAR(number)
♦ Argument Explanation
| Argument | Required/Optional | Explanation |
|---|---|---|
| number | Required | A number between 1 to 255 assigned to a specific character |
♦ Output
The CHAR function will return a character based on the number given as argument.
♦ Availability
This function has been introduced in OFFICE 2010. Any office version since 2010 has this function.
How to Use CHAR Function in Excel: 8 Suitable Examples
Now we will see some examples of using the CHAR function which will help you to understand the function and its usages more clearly.
1. Add Two Strings Using The CHAR Function
You can add two different strings using the CHAR function.
➤ Type the following formula in an empty cell (C7),
=B7 &CHAR(45)& B8The formula will add the strings of the cell B7 and B8 with a hyphen and will give the return in cell C7. If you want to add the two strings with any other character rather than a hyphen, you have to insert a different code. For example, if you want to join the strings by a comma you need to insert 44 as the code or 32 for just a space.
➤ Press ENTER
As a result, you’ll get the two strings joined by a hyphen in cell C7.
2. Add Character to String
You can also add a character to a string by the CHAR function. Suppose, in the following example we want to add a # with the product code. To do that,
➤ Type the following formula in an empty cell (C7),
=B6&CHAR(35)The formula will add a # (character code 35) to the text of cell B6 and will return in cell C7.
➤ Press ENTER
As a result, you’ll see a character # has been added to the text in cell C7.
3. Remove Character from String
You can also remove a character from a string with the help of the CHAR function and the SUBSTITUTE function. To remove the character # from the string of cell B7,
➤ Type the formula in cell C7,
=SUBSTITUTE(B7,CHAR(35),"")Here, the CHAR function will give the character # for the code 35 and the SUBSTITUTE function will remove the character from the cell B7 by replacing it with an empty string.
➤ Press ENTER
You will see the character has been removed.
4. Add Two Strings with a Line Break Using CHAR Function
Another use of the CHAR function is that we can use this function to add two strings with a line break. To do that,
➤ Type the formula in cell C7,
=B7&CHAR(10)&B8The formula will join the texts of cell B7 and cell B8 with a line break as the character code of line break is 10.
➤ Press ENTER
And you will see the text from those two cells are joined together in a cell C7 with a line break.
5. Replace Line Break with Comma by CHAR Function
You can also replace line break with any other character by using the SUBSTITUTE and the CHAR function altogether. In this example, we will see replacing line break with a comma. First,
➤ Type the formula in cell C7,
=SUBSTITUTE(B7,CHAR(10),CHAR(44))The CHAR(10) portions will return a line break and the CHAR(44) portions will return a comma. After that, the SUBSTITUTE function will replace the line break with the comma.
➤ Press ENTER
As a result, you will see the line break has been replaced with a comma.
6. CHAR Function to Create a List of Characters
You can make a list of ASCII code and associated characters with the help of the CHAR function. First,
➤ Type the following formula
=CHAR(ROW())The formula will return the first character.
➤ Press ENTER and drag the cell to the 255th cell from that cell.
As a result, you will get the complete list of the characters. In the image below I’ve shown a fraction of that list. You’ll get the complete list in the practice Excel file.
7. Create List of Alphabets in Excel
You can create a list of alphabets using the CHAR function in Excel.
- To do that, create a list of numbers from 1 to 26 for each alphabet.
- Then use the following formula to create your desired list. Here, we added 64 with each serial number as the code for the alphabet starts from 65.
=CHAR(B5+64)
8. Check If ASCII Values Are Correct for List of Characters
To check if ASCII values for some characters are correct or incorrect, you can use the IF and CHAR function in Excel. We used the following formula to do that.
=IF(CHAR(C5)=B5,"Correct","Incorrect")
💡Things to Remember When Using The CHAR Function
📌 You must input a number between 1 to 255 to the CHAR function. Otherwise, the formula will show #Value! Error.
📌 The code can differ in different operating systems. So, if you use any other operating system rather than Windows, such as Linux or macOS, the code for different characters can be different.
📌 If you enter a non-numeric value the CHAR function will show #Value! Error.
📌 The CODE function can be used as reverse CHAR function that means you can find out the code of any character by the CODE. For example, enter =CODE(“A”), it will return 65.
📌 The CHAR function can not return all the characters. For advanced characters, you can use the UNICHAR function.
Frequently Asked Questions
1. What is the range of values that can be used with the CHAR function?
The CHAR function supports a range of numeric codes from 1 to 255. These codes correspond to the ASCII character set, which includes letters, numbers, symbols, and control characters.
2. Can I use the CHAR function to insert non-printable characters?
Yes, you can use the CHAR function to insert non-printable characters by specifying their ASCII or Unicode codes. Non-printable characters include control characters, such as line breaks, tabs, and carriage returns.
📂Download Practice Workbook
Conclusion
I hope this article has helped you to understand the applications of the CHAR function in Excel. Going through the other articles provided here you can also learn about specific character codes such as Code 9, 10, 32, etc. while using this function. If you have any confusion about the function, please leave a comment. If you know of any additional uses of this function, let me know about that.
