google sheets search - Free Excel Tutorial

November 1, 2021November 1, 2021

Google Sheets SUBSTITUTE Function

This post will guide you how to use Google Sheets SUBSTITUTE function with syntax and examples.

Description

The Google Sheets SUBSTITUTE function replaces a new text string for an old text string in a text string.

The SUBSTITUTE function can be used to replace existing text with new text in a string in google sheets. The purpose of this function is to replace text based on content. And its returned value is the processed text.

The SUBSTITUTE function is a build-in function in Google Sheets and it is categorized as a Text Function.

Syntax

The syntax of the SUBSTITUTE function is as below:

=SUBSTITUTE(text, old_text, new_text,[instance_num])

Where the SUBSTITUTE function arguments are:

Text -This is a required argument. The text string that containing the old text to be substituted with new text string.
Old_text – This is a required argument. The substring that you want to replace.
New_text – This is a required argument. The new substring to replace Old_text substring with.
Instance_num– This is an optional argument. The number value will specify which occurrence of old substring you want to replace with new substring. By default, all occurrences of old_text are replaced.

Note:

If Instance_numvalue is omitted, every occurrence of old substring will be replaced with new substring.
The SUBSTITUTE function used to replace text based on matching and not position.
The SUBSTITUTE function is case-sensitive.
Old_text will match parts of words as well as whole words. For example, searching a string “sh” will also replace text within “sheets”.
SUBSTITUTE function can only be used to replace one or all instances of a string within new_text in a single cell.
SUBSTITUTE function returns text as the output. If a number output is desired, and you can try using the VALUE function in combination with this function.

Google Sheets SUBSTITUTE Function Examples

The below examples will show you how to use google sheets SUBSTITUTE function to replace old substring with a new substring.

#1 To substitute first instance of “google”old text string with a new text string “mygoogle” in B1 cell, just using formula:

=SUBSTITUTE(B1,”google”,” mygoogle”,1).

#2 To substitute second instance of “google”old text string with a new text string “googlefunction” in B1 cell, just using formula:

=SUBSTITUTE(B1,”google”,”googlefunction “,2)

Note: You can use the REPLACE function to replace a newly text string at a given position in a text string. And you can use the SUBSTITUTE function to replace an existing text with a new text in a string. And Using FIND function or SEARCH function to get the location of a given text string in a text sting.

October 31, 2021

Google Sheets SEARCHB Function

This post will guide you how to use Google Sheets SEARCHB function with syntax and examples.

Description

The Google Sheets SEARCHB function returns the number of the starting location of a substring in a text string counting each double-character as 2. You can use the SEARCHB function to locate a text string within another text string and returns the position of the first character of find_text within text.

The SEARCHB function can be used to calculate the position at which a string is first found within text counting each double-character as 2 in google sheets. The purpose of this function is to get the location of text in the given string. And it’s returned value as a number representing the location of finding text.

The SEARCHB function is a build-in function in Google Sheets and it is categorized as a Text Function.

Syntax

The syntax of the SEARCHB function is as below:

= SEARCHB(find_text, within_text,[start_num])

Where the SEARCHB function arguments are:

find_text -This is a required argument. The substring that you want to find
within_text -This is a required argument. The text string in which you want to search for the substring (find_text).
Start_num -This is an optional argument. The starting position to search.

Note:

The SEARCHB function will return the position of a find_text inside another text string. And if there is more than one occurrence of the find string, and the SEARCHB function returns the position of the first occurrence.
If find_text string is not found, the SEARCHB function will return the #VALUE! Error value.
When find text string is empty, the SEARCHB function will return 1.
If start_num value is omitted, it is set to be 1 by default.
The SEARCHB function is not case-sensitive. For example, “google” will match “GOOGLE”. If you wish to compare text where case matters, and you can use the FIND function.
The SEARCHB function support wildcard characters.
you can use another function called IFERROR function in combination with the SEARCHB function to check for cases when there are any matches to the search.
Using SEARCH function for standard character sets and SEARCHB function for double-byte character sets such as Chinese, Japanese, and Korean.

Google Sheets SEARCHB Function Examples

The below examples will show you how to use google sheets SEARCHB function to get the position of substring in a text string.

#1 To get the position of “google” text string in B1 cell, starting at the second position, just using formula:

= SEARCHB(“google”,B1,2) //it returns 7

#2 The search function use wildcard characters to look for a string starts with “go” characters in cell B1, using the following formula:

=SEARCHB(“go*”,B1,1) //it returns 7

#3 #VALUE! Error will be returned if find_text string is not found in with_text:

=SEARCH(“test”,”google”,1)   //it returns #VALUE!

#4 to get the position of a double-byte character sets “新“ within another Chinese text string, using the following formula:

=SEARCHB("新", "农历新年", 2) //it returns 5

October 31, 2021

Google Sheets SEARCH Function

This post will guide you how to use Google Sheets SEARCH function with syntax and examples.

Description

The Google Sheets SEARCH function returns the number of the starting location of a substring in a text string. You can use the SEARCH function to locate a text string within another text string and returns the position of the first character of find_text within text.

The SEARCH function can be used to calculate the position at which a string is first found within text in google sheets. The purpose of this function is to get the location of text in the given string. And it’s returned value as a number representing the location of finding text.

The SEARCH function is a build-in function in Google Sheets and it is categorized as a Text Function.

Syntax

The syntax of the SEARCH function is as below:

= SEARCH (find_text, within_text,[start_num])

Where the SEARCH function arguments are:

find_text -This is a required argument. The substring that you want to find
within_text -This is a required argument. The text string in which you want to search for the substring (find_text).
Start_num -This is an optional argument. The starting position to search.

Note:

The SEARCH function will return the position of a find_text inside another text string. And if there is more than one occurrence of the find string, and the SEARCH function returns the position of the first occurrence.
If find_text string is not found, the search function will return the #VALUE! Error value.
When find text string is empty, the SEARCH function will return 1.
If start_num value is omitted, it is set to be 1 by default.
The SEARCH function is not case-sensitive. For example, “google” will match “GOOGLE”. If you wish to compare text where case matters, and you can use the FIND function.
The SEARCH function support wildcard characters.
you can use another function called IFERROR function in combination with the SEARCH function to check for cases when there are any matches to the search.

Google Sheets SEARCH Function Examples

The below examples will show you how to use google sheets SEARCH function to get the position of substring in a text string.

#1 To get the position of “google” text string in B1 cell, starting at the second position, just using formula:

=SEARCH(“google”,B1,2) //it returns 7

#2 The search function use wildcard characters to look for a string starts with “go” characters in cell B1, using the following formula:

=SEARCH(“go*”,B1,1) //it returns 7

#3 #VALUE! Error will be returned if find_text string is not found in with_text:

=SEARCH(“test”,”google”,1)   //it returns #VALUE!

October 29, 2021November 1, 2021

Google Sheets REPLACEB Function

This post will guide you how to use Google Sheets REPLACEB function with syntax and examples.

Description

The Google Sheets REPLACEB function replaces all or part of a text string with another text string based on a number of bytes.

The REPLACEB function can be used to replace characters specified by position in the given text string with another newly text string in google sheets.

The REPLACEB function is a build-in function in Google Sheets and it is categorized as a Text Function.

Syntax

The syntax of the REPLACEB function is as below:

=REPLACEB (old_text, start_num, num_bytes, new_text)

Where the REPLACEB function arguments are:

Old_text -This is a required argument. The text string that you want to replace all or part of
Start_num – This is a required argument. The position of the first character that you want to replace within old_text.
Num_bytes – This is a required argument. The number of characters that you want to replace within old_text
New_text – This is a required argument. The new text that will replace characters in old_text text string.

Note:

The returned results of REPLACEB function are treated as text string in google sheets and if you are using the REPLACEB function with numbers in calculations, the error message may be returned.
The REPLACEB function returns text as the output. And if you wish to return a number, and you can try to use the VALUE function in combination with the REPLACEB function.
The REPLACEB function takes 4 required arguments. The first argument, old_text, is the text string that a part of which will be replaced. The second argument, Start_num is the position where the replacement will begin to replace. The third argument, Num_bytes is the number of bytes in the text to be replaced. The last argument, New_text is the text which will be inserted into the original text string.
If both Start_num and Num_bytes are not a positive number and the REPLACEB function returns a #VALUE error.
It is designed to work with double-type characters set. Such as: simplified Chinese.
REPLACEB function will return the same value as REPLACE function if the old_text has only single byte characters.

Google Sheets REPLACE Function Examples

The below examples will show you how to use google sheets REPLACEB function to replace part of a text string with another new text string.

#1 To replace 4 bytes in B1 cell with a new text string and starting with 7^th character in old_text text, just using formula:

=REPLACEB(B1,7,4," replace") //it returns “google replace1 sheets 2020”

Note: You can use the REPLACEB function to replace a newly text string at a given position in a text string. And you can use the SUBSTITUTE function to replace an existing text with a new text in a string. And Using FIND function and SEARCH function to get the location of a given text string in a text sting.

October 28, 2021November 1, 2021

Google Sheets REPLACE Function

This post will guide you how to use Google Sheets REPLACE function with syntax and examples.

Description

The Google Sheets REPLACE function replaces all or part of a text string with another text string.

The REPLACE function can be used to replace characters specified by position in the given text string with another newly text string in google sheets.

The REPLACE function is a build-in function in Google Sheets and it is categorized as a Text Function.

Syntax

The syntax of the REPLACE function is as below:

=REPLACE (old_text, start_num, num_chars, new_text)

Where the REPLACE function arguments are:

Old_text -This is a required argument. The text string that you want to replace all or part of
Start_num – This is a required argument. The position of the first character that you want to replace within old_text.
Num_chars – This is a required argument. The number of characters that you want to replace within old_text
New_text – This is a required argument. The new text that will replace characters in old_text text string.

Note:

The returned results of REPLACE function are treated as text string in google sheets and if you are using the REPLACE function with numbers in calculations, the error message may be returned.
The REPLACE function returns text as the output. And if you wish to return a number, and you can try to use the VALUE function in combination with the REPLACE function.
The REPLACE function takes 4 required arguments. The first argument, old_text, is the text string that a part of which will be replaced. The second argument, Start_num is the position where the replacement will begin to replace. The Third argument , Num_chars is the number of characters in the text to be replaced. The last argument, New_text is the text which will be inserted into the original text string.
If both Start_num and Num_chars are not a positive number and the REPLACE function returns a #VALUE error.

There is another replace function called REPLACEB function. It is designed to work with double-type characters set. Such as: simplified Chinese.

Google Sheets REPLACE Function Examples

The below examples will show you how to use google sheets REPLACE function to replace part of a text string with another new text string.

#1 To replace 4 characters in B1 cell with a new text string and starting with 7^th character in old_text text, just using formula:

=REPLACE(B1,7,4," replace") //it returns “google replace1 sheets 2020”

#2 you can use the google sheets REPLACE function to replace unwanted text string in a cell with another text string or empty string.

When you copied or imported data from the external application, it may include unwanted characters or text string along with the good data. At this time, you can use the REPLACE function to remove it.

For example, if you want to replace the first character of the text string “#158” with a dollar sign, you can use the below REPLACE formula:

=REPLACE(B1,1,1,"$")

You will get the result as: $158 in the above formula.

#3 Replace text with an empty string for new text using REPLACE function. For example, replace “google” word with another word “excel”:

=REPLACE(“google sheets”,1,6,”excel”) //it returns “excel sheets”

#4 Using REPLACE function to remove the first 6 characters from a text string in Cell B1, type:

=REPLACE(B1,1,6,"")//it returns “2021 sheets 2020”