Excel Functions Tutorials
- Excel Formulas Cheatsheet
- Excel
- Financial Functions in Excel
- Logical Functions in Excel
- Text and String Functions in Excel
- Concatenate Excel Function
- RIGHT Function in Excel
- LEN in Excel
- LEFT Function in Excel
- Search Function in Excel
- TEXT Function in Excel
- PROPER in Excel
- MID in Excel
- Trim in Excel
- CLEAN Excel Function
- EXACT Excel Function
- REPT Function in Excel
- CODE Excel Function
- FIND Function in Excel
- VALUE Function in Excel
- Char Function In Excel
- Substitute Function in Excel
- Substring in Excel
- Lookup Reference in Excel
- Address Function in Excel
- Choose Function in Excel
- Column Function in Excel
- Columns Function in Excel
- REPLACE Function in Excel
- GetPivotData in Excel
- HLOOKUP in Excel
- Hyperlink Excel Function
- INDIRECT Function in Excel
- LOOKUP Excel Function
- Match Excel Function
- VLOOKUP Excel Function
- INDEX Excel Function
- VLOOKUP vs HLOOKUP
- TRANSPOSE Excel Function
- Row Function in Excel
- OFFSET Excel Function
- VLOOKUP with Multiple Criteria
- IFERROR with VLOOKUP in Excel
- Fixing VLOOKUP Errors
- Alternatives to Vlookup
- Maths Functions in Excel
- POWER Function in Excel
- EVEN Function in Excel
- ODD Function in Excel
- ABS Function in Excel
- SUM Function in Excel
- SUMPRODUCT Function in Excel
- SUBTOTAL Excel Function
- ROUND in Excel
- AGGREGATE Excel Function
- PRODUCT Excel Function
- RAND Excel Function
- LOG Excel Function
- EXPONENTIAL Excel Function
- SUMIF in Excel
- TAN Excel Function
- CEILING Excel Function
- LN Excel Function
- SIGN Excel Function
- COS Excel Function
- FLOOR Function in Excel
- SIN Excel Function
- ROUNDDOWN Excel Function
- ROUNDUP Function in Excel
- DSUM
- COMBIN Excel Function
- INT Excel Function (Integer)
- TANH in Excel
- Date and Time Function in Excel
- Statistical Function in Excel
- AVERAGE Excel Function
- CORREL Excel Function
- COUNT Excel Function
- COUNTIF Excel Function
- FREQUENCY Excel Function
- MAX Excel Function
- MEDIAN Excel Function
- GROWTH Excel Function
- SLOPE Function in Excel
- TREND Function in Excel
- SMALL Function in Excel
- MODE Excel Function
- LARGE Excel Function
- PERCENTILE Excel Function
- LINEST Excel Function
- T-TEST in Excel
- QUARTILE Excel Function
- FORECAST Excel Function
- MIN in Excel
- Standard Deviation in Excel
- COUNTIF with Multiple Criteria
- Mean vs Median
- NORM.S.INV Function in Excel
- NORMDIST in Excel
- Variance vs Standard Deviation
- Information Functions in Excel
- Excel Charts
- Column Chart in excel
- Stacked Column Chart
- Pie Chart in Excel
- Area Chart in Excel
- Stacked Chart in Excel (Column, Bar & 100% Stacked)
- Histogram Excel Chart
- Waterfall Chart in Excel
- Pareto Chart in Excel
- Bubble Chart in Excel
- Gantt Chart in Excel
- Radar Chart in Excel (Spider Chart)
- Clustered Bar Chart in Excel
- Clustered Column Chart in Excel
- Dynamic Chart in Excel
- Excel Tools
- Watch Window in Excel
- List Box in Excel VBA
- Conditional Formatting in Excel
- Data Table in Excel
- Data Validation Excel
- Paste Special in Excel (With Top 10 Shortcuts)
- One Variable Data Table in Excel
- Two-Variable Data Table in Excel
- Scenario Manager in Excel
- Solver in Excel
- Name Range in Excel
- Dynamic Named Range in Excel
- Sort Data in Excel
- Sort by Color in Excel
- Sort by Number in Excel
- Auto Format Excel
- Track Changes in Excel
- Text to Columns in Excel
- Merge Cells in Excel
- Lock Cells in Excel
- Scroll Bars in Excel
- Spell Check in Excel
- Filters in Excel
- Auto Filter In Excel
- Advanced Filter in Excel
- Recording Macros in Excel
- Add-Ins in excel
- Excel Fill Handle
- AutoFill in Excel
- Excel Tables
- Radio Button in Excel
- Combo Box in Excel and VBA
- Checkbox in Excel
- Excel Tips
- How to Unhide Columns in Excel?
- Convert Date to Text in Excel
- Top 20 Keyboard Shortcuts in Excel
- Convert Numbers to Text in Excel
- Show Formula in Excel
- Absolute Reference in Excel
- Relative References in Excel
- Superscript in Excel
- Subscript in Excel
- Formula Errors in Excel
- Convert Text to Numbers in Excel
- Insert Multiple Rows in Excel
- Remove Blank Rows in Excel
- Highlight Every Other Row in Excel
- External Links in Excel
- Insert Hyperlinks in Excel
- Remove Hyperlinks in Excel
- Combine Cells in Excel
- Remove Duplicates in Excel
- Random Numbers in Excel
- Drop Down List in Excel
- Refresh Pivot Table in Excel
- Column Sort in Excel
- Hiding a Column in Excel
- Count Unique Values in Excel
- CAGR Formula in Excel
- Equations in Excel
- How to Delete Pivot Table?
- Excel Subtraction Formula
- Excel Extensions
- Hide Formula in Excel
- Numbering in Excel
- Auto Numbering in Excel
- Row Limit in Excel
- Delete Row Shortcut in Excel
- Arrays in Excel VBA
- Array Formulas in Excel
- Dynamic Tables in Excel
- Convert Columns to Rows in Excel
- Formatting in Excel
- Print Comments in Excel
- Excel vs Google Sheets
CHAR Function in Excel (Table of Contents)
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 the 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 a [space] as a starting character.
We pasted the value using PasteSpecial functionality in cell C2 and we have a [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 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 to 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 the 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 is 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.
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.
You can download this CHAR Function in Excel template here – CHAR Function Excel Template
Recommended Articles
This has been a guide to CHAR Function in Excel. Here we discuss the CHAR Formula and how to use CHAR function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –
Leave a Reply