We don’t always need to manually type on the keyboard to insert a particular character, nor do we always have the option as keyboards are limited in what they can do. There are occasions when manually typing a character is a hassle, either because the character requires multiple key combinations or because the keyboard we’re typing on doesn’t have the characters. Or perhaps we want to insert certain special characters, such as emojis. For all these and many other reasons, we can use the CHAR function to our aid.
What is the CHAR function?
The CHAR function is a function that returns the character specified by a code number corresponding to the character. It takes an ASCII value (ASCII being short for American Standard Code for Information Interchange) or a Unicode value (which can encode more characters than ASCII) as an input number and gives the character associated with that code as an output.
Suppose, you want to insert a man emoji in a row containing a list of men. You can achieve this by using the CHAR function.
The CHAR function can also be used to concatenate a text of characters in different cells and merge them into one cell, such as an address or name of a person mentioned in multiple cells. For example, “John” & “Doe” to “John Doe”.
The CHAR function is a straight-forward function and has only one variable. We need only give it the ASCII or the Unicode number for a character and it will return the character. It is particularly useful to access characters that are not directly available, such as a superscript or certain symbols.
CHAR function formula in Google Sheets
The formula for the CHAR function is:
Where table_number is a decimal value that looks up the Unicode and converts the number into the corresponding character value.
How to use the CHAR function in Google Sheets
To use the CHAR function, you just need to type in the formula below into an empty cell:
Replace the argument inside the parentheses with the code for the character you want to insert in the cell. The Unicode numbers for various characters can be found here.
In order to learn how the CHAR function works and to understand the use cases, let us consider a few examples.
You can make a copy of the spreadsheet below and follow along with the examples.
Example 1: Using the CHAR function to return an alphabet
For this first simple example, we will use the CHAR function to display an alphabet instead of typing the letter on the keyboard. Let the letter be “X”. We’ll need to know the decimal number code for each corresponding character.
To output the capital letter X, we enter the formula below in an empty cell:
This will return the letter “X” since 88 is the code for the character “X”. Do note that small x (“x”) and capital X (“X”) have different codes, as do all the other letters..
Example 2: Using the CHAR function to insert superscript and subscript
Since Google Sheets doesn’t have a function to insert or a superscript or a subscript directly, we can use the CHAR function to perform the task.
Let us assume we’re working with data for a physics study and we need to write the Einstein’s energy-mass equation (E=mc2) in our table. To do so, we find the Unicode value for the power of 2, which is 178. Then we type in the formula with the normal characters enclosed by a pair of quotation marks followed by the symbol “&” and enter the CHAR function with the code number for the desired number to be inserted as superscript, as shown below. The code numbers for each of the digits can be found here.
This will give us the desired format on the sheet, which is E=mc2.
To add more digits to the exponent, we use the above formula and add the CHAR function with the Unicode number for the corresponding number adding the “&” symbol between all of the adjacent characters.
Example 3: Using the CHAR function to display emojis on the sheet
We can also use the CHAR function to insert emojis in the spreadsheet. To do so we need to know the decimal Unicode for the emoji(s) that we want to insert. Here is a list of emojis with decimal Unicode numbers.
Once we have considered the emojis we want to insert and identified their corresponding Unicode numbers, we can simply type the formula in the cell that we want the emoji to be displayed. If we want to insert a checkmark emoji, then the Unicode number is 9989. So the formula will be as under:
We can also use this CHAR function to insert multiple emojis of the same kind or different kinds. To insert more than one emojis, we just need to type in the formula followed by the symbol “&” and add as many emojis as we desire.
The CHAR function along with the REPT function can be used to make a star rating on a sheet. The REPT function repeats a character according to the number specified.
The formula is:
Where 11088 is the Unicode number for the star emoji, and C8 is the cell with ratings in number, which is the number of stars that we want displayed. Instead of C8, you can also specify the number of stars in numbers, 4, that is, and it will return the same result.
On pressing Enter, the rating is displayed in stars. We can then drag down the cell to the last movie and the number of stars will be displayed accordingly.
Example 4: Using the CHAR function to merge texts in different cells
Assume that you have a list of employees with their first name and the last name in different rows. The CHAR function can be used to concatenate the cells and merge the names in one cell.
To accomplish this, we can use the Unicode number for space, which is 32. And we enter the formula on the right of the first list of names, as given under:
Where B3 is the cell number of the first name first in the list and C3 is the cell number of the last name.
The first name and the last name are merged and placed in a single cell upon pressing Enter. Dragging the cell down completes the merging of the remaining names.
The CHAR function can be used in a variety of ways as we have seen in the examples above and for different purposes, not just to insert simple characters. It is not, moreover, limited to the examples that we have discussed. But we hope that these examples have piqued your interest and aroused your enthusiasm to know more about this clever function and Google Sheets in general. You can visit our blog to learn more about Google Sheets, and formulae, tips and tricks therein.