What is CODE Function in Google Sheets?
The CODE function takes a single text character as input and returns its corresponding Unicode value. For the uninitiated, the Unicode is a character encoding standard across platforms that assigns a unique number to every character. It is a unique value for every character and helps sort and process text. The CODE function in Google Sheets can find the Unicode value of the first character in a string. It is helpful when working with different character sets and to perform specific operations based on character codes. CODE in Google Sheets can convert text to numbers, create custom functions, etc.
To understand how it works, in the simple example below, let us find the Unicode value of A and a. Enter the following functions in a sheet.
=CODE(“A”)
=CODE(“a”)
The text should be enclosed within quotes. It is also case-sensitive, as every character represents a different Unicode value.
Key Takeaways
- The CODE function in Google Sheets returns the Unicode value for the first character of a string.
- The syntax for the CODE function is as shown below:
=CODE(text)
Here, text is the string for which you want to find the Unicode value.
- For instance, CODE(“A”) will return 65 because the Unicode code point for “A” is 65.
- The CODE function is a powerful tool used in text analysis, manipulation, and validation in Google Sheets for working at a granular level.
- You can compare characters by their Unicode values using the CODE function.
Syntax
Before we study the function in detail, let us look at its syntax. The syntax for the CODE function is as follows:
=CODE(text)
text – The string for which you want to find the Unicode value.
Note: The CODE function works with just single characters. If the input is a string, it will only evaluate the first character.
How to Use CODE Function in Google Sheets?
As seen above, the CODE function returns the Unicode value of a single character or the first character of a string. The result is a numeric value representing the character’s position in the Unicode standard. There are two ways to enter CODE in Google Sheets.
- Enter CODE manually
- Through the Google Menu bar
Enter CODE manually
Using the CODE function, let us find the Unicode value of a few values.
Open Google Sheets and find an empty cell
Type =CODE(
Step 1: The values in Column A are shown below in Google Sheets. Using the CODE function, we can try to find their Unicode values. Select cell B1 to enter the CODE function. Type =CODE(.
Step 2: Select the cell containing the character to evaluate. Here, we select A1. Close the parenthesis and press Enter. The cell will display the character’s numeric Unicode value.
Step 3: Let’s drag the formula to cell B4 to find the Unicode code point of each value.
As you can see, the numbers have a Unicode code point as well. For the string “Anna,” the function returns the Unicode value of the first character ”A.”
Through the Google Menu bar
The same function can be entered through the Google menu bar by accessing the following.
- Choose the cell where you want to enter the formula.
- Go to the menu bar and click on “Insert” ➝ “Function” ➝ “Text ➝ “Code.”
Once the function is entered in Google Sheets, enter the required argument and press Enter.
Examples
The CODE function only returns the code point for the first character in the provided text. You must extract each character individually to get the Unicode for additional characters in the string. Let us look at an interesting bunch of CODE function in Google Sheets examples below.
Example #1
In this example, we have a string, and we will find the Unicode value of each character of the string. Let us look at it with an example. We have the string entered as shown below.
Step 1: To find the Unicode value of each character in the string “ROSE” in Google Sheets, you have to extract each character individually and apply the CODE function. For this, we use the LEFT and MID functions. Enter the CODE function as shown below. Press Enter.
=CODE(LEFT(A1,1))
Step 2: Now, let us find the Unicode values of O, S and E.
For O, apply the following function.
=CODE(MID(A1,2,1))
Step 3: For S, apply the following function.
=CODE(MID(A1,3,1))
Step 4: Similarly, for T, use the RIGHT function. You can also use different combinations of the MID function in Google Sheets..
=CODE(RIGHT(A1,1))
You can also use =CODE(MID(A1,4,1))
Example #2 -Find Unicode value of the First character
Below is a list of strings available in a table. We must use CODE in Google Sheets to calculate the Unicode value of their first character. We can then add some value to it and replace the first character. Let us see how to go about this fun example.
Step 1: As we all know by now, apply the code function to the first string as follows.
=CODE(A1). Press Enter.
Step 2: Drag the formula down to apply it to each of the strings. You get the Unicode of the first letter of each string.
Let us add some random number, say 10, to this Unicode value and convert it back to a string. To perform the string conversion, you can easily use the CHAR function.
Apply the following function in C1.
=CHAR(B1+10)&MID(A1,2, LEN(A1)-1)
Explanation:
CHAR(B1+10) = Here, we add 10 to the Unicode value of the first character. Then, we convert this Unicode value back to a character.
MID(A1,2,LEN(A1)-1) – In this interesting part, we use the MID function on the string in A1. We start from the second character of the word and find the rest of the string by giving the length as LEN(A1)-
& – This appends both parts. Now check the result.
Step 3: Drag it for all the strings. Interesting, isn’t it? The CODE function can help in encoding or decoding data as seen in this example.
Example 3 – Using CODE with IF Function
Suppose we have some names in a sheet. We wish to find those that begin with a capital letter. This activity is simple enough when we use the CODE function. Let’s see how to go about it.
Step 1: Set up the data in a Google Sheet, as shown below.
Step 2: Let us apply the CODE function. Here, we want to find those names which begin with capital letters. Apply the following function to B1.
=IF(AND(CODE(A1) > 65,CODE(A1) < 90), “Capital Letter”, “Small Letter”)
Here,
- We use the AND function to find if the UNICODE of the first character in A1 is greater than 65 or less than 90, which represents the Unicode value of A to Z.
- If the condition is TRUE, it starts with a “Capital Letter”. Else, it starts with a “Small Letter.”
- Press Enter. Now, we get the result for the first value. Press Enter. Now, we get result for the first value.
Step 3: Drag the formula to B6 to get the results for the other values.
Important Things to Note
- To get the Unicode value of characters beyond the first in a string, you can use the MID function to extract a particular character and pass it to the CODE function.
- If the text is empty or the cell has no characters, the CODE function will return a #VALUE error.
- By combining CODE with IF, you can create complex logic based on the Unicode values of characters for data analysis.
- You can check if a character is a letter, or a number based on its Unicode value. For instance, A-Z have Unicode values from 65 to 90, a-z have Unicode values from 97 to 122 and 0-9 have Unicode values from 48 to 57.
Frequently Asked Questions (FAQs)
1. Getting a character’s numeric value using CODE in Google Sheets can be particularly helpful when dealing with data for character encoding. It is also useful when automating or streamlining text-processing tasks.
2. The CODE function can help you detect characters like line breaks or tabs that have specific Unicode values, such as line break 10 and tab 9.
3. It is useful in input validation, where you need to validate whether a character is of a specific type, such as letters or numbers.
4. You can compare two characters by their Unicode values which is useful in sorting according to alphabetical or numeric order.
The CODE function works with single characters only. In case you have a text value, it will only evaluate the first character.
When working with international datasets, different locales may have different Unicode values for some characters which could affect the functionality of CODE in Google Sheets.
If the text is empty, the CODE function will return an error.
While CODE gives you the Unicode value of a character, the CHAR function is the opposite, giving you the character corresponding to a specific Unicode code point.
The CODE function can return the Unicode values for not just numbers and text, but special characters as well. For example: CODE(CHAR(10)) returns 10. For a Tab, the result is 9.
Download Template
This article must help understand CODE in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is CODE in Google Sheets. We learn its syntax & how to use it to get the UNICODE value of a character with examples. You can learn more from the following articles. –
Leave a Reply