Excel Char Function with Examples

0 Flares Filament.io 0 Flares ×

The CHAR function of 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

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

Using the CHAR function to insert line breaks

The line break is character 10 from my keyboards character set. I looked that up before writing the formula.

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 CHAR function, to remove these characters (this tutorial shows four more ways to clean text in a cell).

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),", ")

Remove unwanted characters with the CHAR function

More Excel Formula Tutorials

Leave a Reply

Your email address will not be published. Required fields are marked *