This guide will explain how to use the FINDB function in Google Sheets.
Table of Contents
In the context of text encoding, double-byte characters refer to characters that require two bytes to represent. Double-byte characters are often used in character sets that include large numbers of symbols, such as those required for Chinese, Japanese, and Korean languages.
We can use the
FINDB function when we need to find the position of a specific word or phrase in a larger text string that includes double-byte characters. This could be useful in processing and analyzing multilingual datasets.
In this guide, we will provide a step-by-step tutorial on how to use the
FINDB function in Google Sheets to find a character within a string with double-byte characters.
The Anatomy of the FINDB Function
The syntax of the
FINDB function is as follows:
=FINDB(search_for, text_to_search, [starting_at])
Let’s look at each argument to understand how to use the
- search_for refers to the string you want to look for.
- The text_to_search parameter specifies the text you want to search through for the first occurrence of the search_for argument.
- starting_at is an optional argument that determines the starting position within text_to_search at which to start the search.
- If search_for is not found, the #VALUE! error value is returned.
A Real Example of Using the FINDB Function in Google Sheets
Let’s explore a simple example of a spreadsheet where we’ll use the
In the table above, we have the column search_for containing a character we want to search for and another column text_to_search specifying the string we’ll search through.
Since some of these strings have two-byte characters, we’ll need to use the
FINDB function instead of the
FIND function to determine the byte position of the character we’re searching for.
We can use the following formula to find the character’s position:
FINDB formula, we can determine that the “ん” character is located at the third byte of the string “こんにちは”.
Adding a starting_at argument for FINDB
FINDB function only returns the first instance of the string you’re searching for, you may want to use the optional third argument to set where to start the search.
For example, we can use the following formula to start our search at the 10th byte of the given string:
Adding the starting_at argument, we were able to determine the byte location of the second instance of the “ん” character in the text found in cell B9.
Using FINDB with Other Functions
We can combine the
FINDB function with other Google Sheets functions to solve a variety of use cases involving text. For example, we can use the
LEFT function together to extract a username from an email address.
In the table above, we have a list of email addresses from users who have signed up for our mailing list. We want to extract the username of each account and place it in another column.
We can use the following formula to extract the username from the email address:
=LEFT(A2, FINDB("@", A2) - 1)
The formula above works by using the
FINDB function to find the first occurrence of the “@” symbol. The
LEFT function then returns a substring from the beginning of the email address up to the “@” character. However, since we want to exclude the “@” character itself, we’ll subtract the result of
FINDB by 1.
Click on the link below to create your own copy of our examples.
Head to the next section to read our step-by-step tutorial on how to start using the
FINDB function in Google Sheets.
How to Use the FINDB Function in Google Sheets
- Select a cell where you want to place the
- Provide the search_for and text_to_search arguments.
In cell C2, we used the formula FINDB(A2,B2) to find the location of the character “ん” in the string “こんにちは”.
- Hit the Enter key to evaluate the
- We can use the AutoFill feature to copy formula to the remaining cells in the column.
- You may also include a number for the starting_at argument to specify where to begin the text search.
In the example above, we’ll use the formula =FINDB(A9,B9,10) to set up our function to count starting at the 10th byte of the given string.
These are all the steps you need to know to start using the
FINDB function in Google Sheets.
- How is the FINDB function different from FIND?
Both functions help in finding a substring within a given string. The primary difference is that the
FINDBfunction considers different scripts and characters in different languages and is preferred when handling double-byte character sets like Japanese and Korean.
- What happens if the starting_at value argument is larger than the length of the text_to_search argument?
If the starting_at value is larger than the length of the value in text_to_search, the
FINDBfunction will return a #VALUE! error.
If you found this article helpful, you may also be interested in our guide on how to use the
LENB function to find the length of a string in bytes.
That’s all for this guide! Be sure to check out our library of spreadsheet resources, tips, and tricks!