Prantick Bala
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 lower case alphabets a-z have ASCII values ranging from 097 to 122.
📂Download Practice Workbook
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.
6 Suitable Examples of Using The CHAR Function in Excel
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.
Read More: How to Combine Text and Numbers in Excel and Keep Formatting
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.
💡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.
Conclusion
I hope this article has helped you to better understand the applications of the CHAR function in Excel. If you have any confusion about the function please leave a comment. If you know any additional uses of this function, let me know about that.
Related Articles
- How to Show Percentage in Legend in Excel Pie Chart (with Easy Steps)
- Use FIXED Function in Excel (6 Suitable Examples)
- How to Use TEXT Function to Format Codes in Excel
- Auto Generate Number Sequence in Excel (9 Examples)
- How to Use Format Function in Excel (with Suitable Examples)
- Show Grand Total in Pivot Table (3 Easy Methods)
