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:
The character used for line breaks is different depending on whether Excel is running on Mac or Windows: CHAR(10) for Windows, CHAR(13) for Mac. This makes it tricky to write a single formula that will work as expected...
This formula takes advantage of the fact that TRIM will remove any number of leading spaces. We look for line breaks and "flood" the text with spaces where we find one. Then we come back and grab text from the right....
First, the LEN function counts total characters in the cell B5.
Next SUBSTITUTE removes all "line returns" from the text in B5 by looking for CHAR(10) which is the character code for the return character in Windows....
In this example we are looking for the nth occurrence of the "@" character.
Working from the inside out, we first use the SUBSTITUTE function to replace the nth occurrence of "@" with CHAR(160):
To include double quotes inside a formula, you can use additional double quotes as escape characters. By escaping a character, you are telling Excel to treat the " character as literal text. You'll also need to include...
The SUBSTITUTE function can find and replace text in a cell, wherever it occurs.
In this case, we are using SUBSTITUTE to find a character with code number 202, and replace it with an empty string (""), which...
The REPT function simply repeats values. For example, this formula outputs 10 asterisks:
=REPT("*",10) // outputs **********
You can use REPT to repeat any character(s) you like. In this example, we use...
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.