Char Function In Excel

Char function in excel is also known as the character function in excel which is used to identify the character based on the number or integer which is accepted by the computer language for example for character “A” the number is 65 so if we use =char(65) we get A as a result, this is a text function in excel.

CHAR In Excel

CHAR Function in Excel is a function that returns the character specified by the code number (also called the ASCII code) from the character set for your computer. CHAR is categorized as String/Text Function. It takes ASCII value as an input number and gives the character associated with that ASCII value as an output.

ASCII stands for American Standard Code For Information Interchange, is a character encoding standard for digital communications.  For each character that can be typed has a unique integer number associated with it, may it be a character set, digits, punctuation marks, special characters, or control characters. For example, the ASCII value for [space] is 032. ASCII values ranging from 097-122 are reserved for the alphabets a-z in lower case.

In Excel, the CHAR Function in Excel takes the number as input, which is actually the ASCII values, and returns the associated character with it. For example, when we pass 32 as an input, we get a space value as an output.

If we copy and PasteSpecial the value of cell B2 to another cell, we will find an [space] as a starting character.

We pasted the value using PasteSpecial functionality in cell C2, and we have an [space] as a character as an output.

CHAR Formula in Excel

Below is the CHAR Formula in Excel

This CHAR takes a single value as a number where the number is between 1-255.

Note: Depending upon the operating system, the character code may vary, so the output may vary on a different operating system for the same input. The WINDOWS operating system uses the ANSI character set while the MAC OS uses the Macintosh Character set.

CHAR Formula has many uses. It can be used to replace unwanted characters. It can further be used while writing an Excel application or a Macro when dealing with strings and characters.

How to Use CHAR in Excel

Excel CHAR function is very simple and easy to use. Let us understand the working of (CHAR) CHARACTER Function in excel by some examples.

CHAR in Excel Example #1

We have a list of Characters given in column A with their respective ASCII values, but few of the ASCII values are not correct. The user needs to find whether the ASCII values are correct or not and color the incorrect ASCII values using the conditional formatting.

In order to find the ASCII values are correct or not, we will use the IF condition with CHAR function in Excel. The formula that we will be using will be

IF CHAR (ASCII value of given character) matches or equals the given ASCII values, then Print Correct Else Print Incorrect.

In Syntax, using the reference values, the CHAR formula in Excel will be

=IF(CHAR(B2)=A2,”Correct”,”Incorrect”)

Applying the above CHAR Formula in the other cells, we have

For conditional formatting, select the range for which we want to apply the condition; here, the range is C2:C13.  Go to Home-> Conditional Formatting -> Highlight Cells Rules ->Text that Contains..

 

Then, type in the Text that a cell contains, to get the cell formatted with the color you may specify and Enter OK.

Output:

CHAR in Excel Example #2

A user downloaded a set of data from the Web, and the data downloaded in Excel was in the format given below.

But the user wants the data in a format like Word1-Word2-Word3 and similarly for the data in other cells.

So, we have to first replace or substitute the unwanted characters, and then we will replace the line breaks to get the desired output.

In order to replace the unwanted characters, we need to know the ASCII code, so in order to get the ASCII code, we can use the function named CODE that returns the ASCII value of a character.

The Values that are returned by the Code Function can be used as an input for the (Char) CHARACTER Function in Excel. Further, to replace the unwanted character, we will use the SUBSTITUTE function in excel.

So the CHAR Formula will be

=SUBSTITUTE(SUBSTITUTE(A2,CHAR(CODE(“?”)),””),CHAR(CODE(““)),”-“)

First, we calculated the ASCII code for the ‘?’, which is 63, and for the ‘line break,’ the ASCII code is 10; we calculated both values using the CODE function and replaced it for the required output.

Applying the CHAR formula to other cells, we have

Output:

CHAR in Excel Example #3

We have two strings in Cell B2 and B3, and we want to concatenate both the strings with a line break in a cell B4.

We will use the CHAR in Excel to insert a line break. We know the ASCII code for a line break is 10, so the CHAR formula will be

=B2&CHAR(10)&B3

After applying the CHAR formula, we get

Now, to make the content of the B4 more readable and show the line break, we will wrap text the content of B4.

Output:

Things to Remember About CHAR Function in Excel

• The (CHAR) CHARACTER function in Excel was introduced in Excel 2000 and later versions of Excel.
• It recognizes the input number between 1-255
• It can be used as the reverse of the (CHAR) CHARACTER function in Excel, as shown in the example.
• We get #VALUE! Error, when we don’t supply the number as an input for the (CHAR) CHARACTER function in Excel, and the number is less than 2 or greater than 254.

Recommended Articles

This has been a guide to CHAR Function in Excel. Here we discuss the CHAR Formula and how to use the CHAR function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

• ISBLANK in Excel
• Convert Numbers to Text in Excel
• Convert Date to Text in Excel
• FORECAST in Excel