Contents
Yesterday in the marathon 30 Excel functions in 30 days we determined the character code using the function CODE (CODE), and also used it to expose hidden symbols. On the eighth day of our marathon, we will study the evil twin of the function CODE (CODE) – function Char (SYMBOL). Possibly a function Char (SYMBOL) Not such an evil twin, just the opposite personality.
So, let’s take a closer look at the instructions for the function Char (SYMBOL) and examples of its use, and if you have something to add to the instructions or examples, write in the comments.
Function 08: CHAR
Function Char (CHAR) returns a specific character whose code matches the entered number, based on your computer’s character table. (For Windows it is ANSI, and for Macintosh – encoding Macintosh).
How can you use the CHAR function?
Function Char (SYMBOL) can solve the following tasks:
- Add a line break to the formula.
- Find a specific letter of the alphabet.
- Make a list of all letters of the alphabet.
- Create a lookup table with character codes.
Syntax CHAR (CHAR)
Function Char (CHAR) has the following syntax:
CHAR(number)
СИМВОЛ(число)
- number (number) – a number from 1 to 255 that specifies the symbol you want to receive. The character is taken from the character table used on your computer.
Traps CHAR (CHAR)
Same as for the function CODE (CODE), result Char (CHAR) may vary depending on the operating system. The codes for the ASCII character table (32 to 126) mostly correspond to the characters on your keyboard. However, the characters for higher numbers (from 129 to 254) may differ.
Example 1: Adding a Line Break
To add a line break in a cell, you can click Alt + Enter. When you concatenate text strings in a formula, you must use the line break character.
=C2&CHAR(10)&C3
=C2&СИМВОЛ(10)&C3
After you create the formula, format the cell with the command Wrap Text (Wrap Text) to see a line break instead of a mysterious character between lines.
Example 2: Finding a letter in the alphabet
Fast! What is the 19th letter of the alphabet? With function Char (SYMBOL) You figure it out without listing the entire alphabet in your mind.
First of all, you need to use the function CODE (CODE) find out the code of the first letter of the alphabet – А. You can enter А into a cell and refer to it, or enter a letter А to the function as a text string.
=CODE("A") или =CODE(B1)
=КОДСИМВ("А") или =КОДСИМВ(B1)
In this example, the code 65 corresponds to the first letter of the English alphabet. For the alphabet, this is the code 192.
Then you can use the function Char (CHAR) to find any letter of the alphabet by adding 64 to the given number.
=CHAR(B5+64)
=СИМВОЛ(B5+64)
Example 3: Listing the entire alphabet
Using Excel’s auto-complete tool, you can write two numbers and quickly create a whole series of numbers. Unfortunately, this does not work with letters. However, you can use the function Char (CHAR) to create a list of all letters of the alphabet.
Use the same technique with the addition 64 to the character number to get the letters of the alphabet.
=CHAR(A2+64)
=СИМВОЛ(A2+64)
Create a custom list
If you want to enter the alphabet using autocomplete, it is possible to import a list of alphabetic characters into a custom list.
- Copy the cells with the letters of the alphabet and paste them on a separate line as values.
- Select these cells. The main thing here is not to confuse and select cells with values, and not with a function Char (SYMBOL).
- Нажмите кнопку Office (Office) on the Excel Ribbon, then select Excel Options (Excel Options). If you have Excel 2010, then select Options (Settings).
- In the category list, click Popular (Basic). If Excel 2010 then Advanced (Additionally).
- Find and click Edit Custom Lists (Change lists…)
- Нажмите кнопку Import (Import), then OK and close the settings menu.
- Now type any letter in the excel sheet and use autocomplete to complete the sequence.
Example 4: Creating a symbol lookup table
You can create a lookup table of all printable characters using the function Char (SYMBOL). Such a guide will be a quick help if you want to enter a character from the keyboard (using the key Alt and 4-digit code on the numeric keypad).
- Write down the numbers from 32 to 255 in the table.
- In the adjacent column, using the function Char (CHAR), display the characters corresponding to these numeric codes.
To show characters of another font such as Symbol or Wingdings, change the font in the column containing the function Char (SYMBOL).