The CHAR function in Excel returns a character specified by a code, from the character set of your computer.
In this blog post, we look at why you might want to use the CHAR function, and some examples of it in action in Excel.
The CHAR function is typically used to insert, or to find, a character which you ordinarily cannot type from your keyboard.
To use it you will need to find out what the code is for the character you want to use.
You can search online for a full list of these character sets so that you know each code (or at least the one you need at the given time). These character sets differ between the Windows and Mac computers, and are also dependent upon the language used.
Watch the Video – Excel CHAR Function
Insert Line Breaks when Concatenating Text
The most common use of the CHAR function is surely to insert line breaks when concatenating text with a formula.
When typing in Excel you can use the Alt + Enter keys together to insert a line break manually. But if you are dealing with hundreds or thousands of downloaded data, that you want to present differently, then the CHAR function is here to help.
In the example below the formula was used in cell E2 to join the text from each cell together with a line break between each cell content. The text from the cells in column E also needing wrapping.
=A2&CHAR(10)&B2&CHAR(10)&C2&CHAR(10)&D2
The line break is character 10 from my keyboards character set. I looked that up before writing the formula.
Excel CHAR Function to Remove Unwanted Characters
Another useful CHAR function example would be to remove unwanted characters from cells.
When downloading data, or copying from the web, you can receive what may look like strange characters. You need these removed before you can start analysing your data.
In column A of the image below the text is on separate lines and a strange symbol has appeared before each word.
The SUBSTITUTE function can be used, with the Excel CHAR function, to remove these characters.
The formula below used two SUBSTITUTE functions. The first one removes the strange question mark symbol, the second one then removes the line break and replaces it with a comma and space.
=SUBSTITUTE(SUBSTITUTE(A1,CHAR(12),""),CHAR(10),", ")
Leave a Reply