How to Use CHAR Function in Excel (6 Suitable Examples)

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.

Excel CHAR Function Overview


📂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)

Syntax

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.

output

♦ 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)& B8

The 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.

Join two string

➤ Press ENTER

As a result, you’ll get the two strings joined by a hyphen in cell C7.

Excel CHAR function


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.

add character

➤ Press ENTER

As a result, you’ll see a character #  has been added to the text in cell C7.

Excel CHAR function


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.

substitute

➤ Press ENTER

You will see the character has been removed.

Excel CHAR function


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)&B8

The formula will join the texts of cell B7 and cell B8 with a line break as the character code of line break is 10.

line break

➤ Press ENTER

And you will see the text from those two cells are joined together in a cell C7 with a line break.

excel char function


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.

excel char function

➤ Press ENTER

As a result, you will see the line break has been replaced with a comma.

replace line break


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.

excel char function


💡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

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo