SEARCHBformula is used to find the starting position of a specific text string in another text string, but it is case-insensitive. It returns the position of the first character of the searched string within the searched text, or
#VALUE!if the searched text is not found. The formula is most commonly used to search for text within a larger string, and it can be used in conjunction with other formulas to manipulate the resulting value.
- How to use
- Examples of using
SEARCHBformula not working?
- Similar formulas to
SEARCHB formula with the syntax shown below, it has 2 required parameters and 1 optional parameter:
- search_for (required):
The text string that you want to search for within the larger text string. This parameter can be either a string itself, or a reference to a cell containing the string.
- text_to_search (required):
The larger text string that you want to search within. This parameter can be either a string itself, or a reference to a cell containing the string.
- starting_at (optional):
An optional parameter that specifies the position within the larger text string to start the search from. If this parameter is not provided, the search will begin at the first character of the larger text string.
ExamplesHere are a few example use cases that explain how to use the
SEARCHBformula in Google Sheets.
Finding a keyword within a string
You have a large set of data, and you want to find all instances of a specific keyword within a text string within that data. You can use the
SEARCHB formula to do this, and then use other formulas like
SUMIF to manipulate the resulting values.
Extracting a substring from a larger string
You have a large text string, and you want to extract a smaller substring from it based on some criteria. You can use the
SEARCHB formula in combination with
RIGHT to extract the desired substring.
Checking if a string contains a specific substring
You want to check if a larger text string contains a specific substring, and return a boolean value indicating whether or not it does. You can use the
SEARCHB formula to do this, and then use an
IF statement to return the appropriate value.
SEARCHBnot working? Here are some common mistakes people make when using the
SEARCHBGoogle Sheets Formula:
Not using double-byte character
If you are searching for a double-byte character, make sure to use the double-byte version of that character in the
Not using quotes around text to search
If you are searching for a string of text, make sure to use quotes around the
Not accounting for starting position
If you want to search for a character or string starting at a specific position in the text string, make sure to include the
starting_at argument and specify the starting position.
The following functions are similar to
SEARCHB or are often used with it in a formula:
SEARCHformula is used to find the position of a piece of text within a larger string of text. It returns the position of the first character of the searched text in the text being searched. This formula is case-insensitive, meaning it will find all instances of the searched text regardless of capitalization.
FINDformula in Google Sheets searches for a specific text within another text and returns the position of the first occurrence of the text. This formula is often used to extract a substring from a larger string or to check if a certain text exists within another text.
MATCHformula is used to search for a specified value within a range and returns the relative position of the value within the range. This formula is commonly used in conjunction with the
INDEXformula to retrieve a value from a specific cell in a range. The
search_typeparameter is optional and determines the type of match to be performed, such as exact match, less than, or greater than.
LEFTformula is used to extract a specific number of characters from the beginning of a text string. It is most commonly used to extract the first name or last name from a full name or to extract a date from a text string.
RIGHTformula in Google Sheets returns the rightmost characters from a string, based on the number of characters specified. It is commonly used to extract a certain number of characters from the end of a string. The formula takes two arguments: the string from which to extract the characters, and the number of characters to extract.
You can learn more about the
SEARCHB Google Sheets function on Google Support.