The Excel CHAR function returns a character when given a valid character code. CHAR can be used to specify characters that are hard to enter in a formula. For example, CHAR(10) returns a line break on Windows, and CHAR(13) returns a line break on the Mac.
Get a character from a number
A single character specified by a number.
number - A number between 1 and 255.
Use CHAR to translate code page numbers into actual characters.
CHAR can be useful when you want to specify characters in formulas or functions that are awkward or impossible to type directly. For example, you can use CHAR(10) to add a line break in a formula on Windows, and CHAR(13) to add a line break on the Mac.
The character returned is based on the character set used by your computer:
To remove specific unwanted characters in Excel, you can use a formula based on the substitute function.
In the example shown, the formula in C4 is:
Which removes a series of 4...
To get the last word from a text string, you can use a formula based on the TRIM, SUBSTITUTE, RIGHT, and REPT functions.
In the example shown, the formula in C5 is:
To add a line break taking into account the current OS (Mac or Windows), you can use the INFO function to test the system and then return the correct break character — CHAR(10) for Windows, CHAR(13) for Mac.
To find the nth occurence of a character in a text string, you can use a formula based on the FIND and SUBSTITUTE functions.
In the example shown, the formula in D5 is:
If you need to include double quotes inside a formula, you can use additional double quotes as "escape characters". By escaping a character, you are asking Excel to to treat the " character as literal text. As always,...
To create a simple in-cell histogram, you can use a formula based on the REPT function. This can be handy when you have straightforward data, and want to avoid the complexity of a separate chart.
The Excel CODE function returns a numeric code for a given character. For example, CODE("a") returns the code 97. The CODE function can be used to figure out numeric codes supplied to the CHAR function.
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.