google sheets find - Free Excel Tutorial

March 21, 2022March 21, 2022

Count Cells that are Case Sensitive in Google Sheets

If you are a frequent user of Google Spreadsheets, you may have come across situations where you needed to add or count the cells that were case-sensitive. If you have done this task manually, I would like to point out that it is the most inefficient way to do it, particularly when Google Spreadsheets has the versatility to do it in a matter of seconds. As a result of attentively reading this article, you will learn about the many adding or counting case-sensitive cells.

counting the cells that are case sensitive in google sheets1

Simple generic formula:

=SUMPRODUCT(--ISNUMBER(FIND(value, range)))

Summary

You may use a formula depending on the ISNUMBER and FIND functions and the SUMPRODUCT function to calculate the number of cells that contain specified text (i.e., hold a substring), taking into consideration upper and lower case. D1 in the example displayed has the following formula, which has been written down:

=SUMPRODUCT(--ISNUMBER(FIND(C1,$A$1:$A$6)))

Let’s See How This Formula Works

In this example, the purpose is to count the number of times each code appears as a substring in a case-sensitive manner, as shown below. When calculating text values, the functions COUNTIF and COUNTIFS are excellent choices. However, since these functions are not case-sensitive, they cannot be utilized to address this issue. To check for substrings, the FIND function should be used in conjunction with the ISNUMBER function, and the SUMPRODUCT function should be used to sum up, the results.

= FIND(C1,$A$1:$A$6)

The FIND function is case-sensitive and accepts three parameters: find text, within the text, and start num. It is also case-sensitive and takes three arguments. Here is the text we are searching for, and this is the text that we are searching inside. Start num is the number of characters to begin searching in find text. Because start num defaults to 1, we are not supplying a number in this instance. This is necessary since we always want FIND to begin with the first character. When finding the text is discovered inside within text, FIND returns the position of the found text as a number: When finding the text is located within text, FIND returns the position of the found text as a number:

counting the cells that are case sensitive in google sheets1

Note: When the find text function fails to yield a result, FIND returns the #VALUE! Error.

=ISNUMBER(FIND(C1,$A$1:$A$6))

This implies that we may be using the ISNUMBER function to transform the output of the FIND function into a TRUE or FALSE value using the ISNUMBER function. A TRUE value will arise from any number, while a FALSE value will result from any error:

As demonstrated in the example, we have four substrings in column C and a variety of codes in columns A1:A6, representing the data for the designated range. Specifically, we want to know how many times each substring in C1:C4 occurs in A1:A6, and we want to know how many times each substring is case-sensitive.

Related Functions

Excel ISNUMBER function
The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
Google Sheets FIND function
The Google Sheets FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])…
Google Sheets SUMPRODUCT function
The Google Sheets SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
Google Sheets COUNTIF function
The Google Sheets CHOOSE function returns a value from a list of values based on index. The syntax of the CHOOSE function is as below:=CHOOSE (index_num, value1,[value2],…) …
Google Sheets COUNTIFS function
The Google SheetsCOUNTIFS function returns the count of cells in a range that meet one or more criteria. The syntax of the COUNTIFS function is as below:= COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)…

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 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”

October 19, 2021October 30, 2021

Google Sheets LENB Function

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

Description

The Google Sheets LENB function returns the length of a text string in bytes.

The LENB function can be used to count the number of bytes of a given text string including space or nonprint characters in google sheets. If a text string is a double byte character set, for example, Japanese, Chinese and Korean, each character should be counted as 2 bytes.

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

Syntax

The syntax of the LENB function is as below:

= LENB (text)

Where the LENB function arguments are:

Text -This is a required argument. The text string that you want to get the length of.

Note:

The LENB function only takes one argument. It counts the number of bytes in text string, including space or non-printing characters. If the LENB function returns unexpected values, and you need to make sure that there are no such characters in given text string.
If text string is an empty string or cell reference to an empty cell, the LENB function returns
LENB function will also count the characters in numeric value.

Google Sheets LENB Function Examples

The below examples will show you how to use google sheets LENB Text function to get the length of a text string in bytes.

#1 To get the number of bytes in the B1 cell, just using formula:

=LENB(B1)

#2 To get the Length of the text string in B2 Cell, just using formula:

=LENB(B2)

#3 Using LENB function to count the number of bytes in a text string:

=LENB(“google”)   //it returns 6

#4 count the number of bytes in a text string in which contain a space character:

=LENB(“google      “)  //it returns 9

The LENB function can be used with the LEFT function and FIND function to extract a substring text to the left of a given text string .

Related Functions

Google Sheets FIND Function
The Google Sheets FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num]) …
Google Sheets LEFT Function
returns a substring (a specified number of the characters) from a text string, starting from the leftmost character. The syntax of the LEFT function is as below:= LEFT(text,[num_chars]) …

October 18, 2021October 30, 2021

Google Sheets LEN Function

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

Description

The Google Sheets LEN function returns the length of a text string (the number of characters in a text string).

The LEN function can be used to count the number of characters for a given text string in google sheets. It also can count characters in numbers, but it will not count the number formatting.

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

Syntax

The syntax of the LEN function is as below:

= LEN(text)

Where the LEN function arguments are:

Text -This is a required argument. The text string that you want to get the length of.

Note:

The LEN function only takes one argument. It counts the number of characters in text string, including space or non-printing characters. If the LEN function returns unexpected values, and you need to make sure that there are no such characters in given text string.
If text string is an empty string or cell reference to an empty cell, the LEN function returns
Len function will also count the characters in numeric value

Google Sheets LEN Function Examples

The below examples will show you how to use google sheets LEN Text function to get the length of a text string.

#1 To get the number of characters in the B1 cell, just using formula:

=LEN(B1)

#2 To get the Length of the text string in B2 Cell, just using formula:

=LEN(B2)

#3 Using LEN function to count the number of characters in a text string:

=LEN(“google”)   //it returns 6

#4 count the number of characters in a text string in which contain a space character:

=LEN(“google      “)  //it returns 9

The LEN function can be used with the LEFT function and FIND function to extract a substring text to the left of a given text string .

Related Functions

Google Sheets FIND Function
The Google Sheets FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num]) …
Google Sheets LEFT Function
returns a substring (a specified number of the characters) from a text string, starting from the leftmost character. The syntax of the LEFT function is as below:= LEFT(text,[num_chars]) …

October 17, 2021October 30, 2021

Google Sheets LEFTB Function

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

Description

The Google Sheets LEFTB function returns a substring (a specified number of the bytes) from a text string, starting from the left portion.

The LEFTB function can be used to extract the left portion of a string up to a certain number of bytes in google sheets.

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

Syntax

The syntax of the LEFTB function is as below:

= LEFTB (text,[num_of_bytes])

Where the LEFTB function arguments are:

Text -This is a required argument. The text string that you want to extract from.
num_of_bytes -This is an optional argument. It will specify the number of bytes that you want to extract from Text string.

Note:

The num_of_bytes value must be greater than or equal to zero. If num_of_bytes value is greater that the length of text string, it will return all of text string. If num_of_bytes value is omitted, it is set to be 1 by default.
If NUM_of_bytes value equal to 0, it will cause LEFT function to extract the empty string.
If text string has only single bytes characters and the LEFTB function returns the same value as LEFT function.

Google Sheets LEFTB Function Examples

The below examples will show you how to use google sheets LEFTB Text function to extract the leftmost substring from a text string.

#1 To extract the leftmost 4 bytes in the B1 cell, just using formula:

=LEFTB(B1,4)

#2 If you do not provide num_of_bytes argument, it will use the default value as 1, using the following formula:

=LEFTB(“google”)

#3 If Num_chars exceeds the text string length, the LEFTB function will return the entire string:

=LEFTB("google",200)

#4 If Text value is a numeric value, and the LEFTB function will return a text value:

=LEFTB(1234,2)

Note:

You can use the LEFTB function to extract substring from the left side of a text string in Google Sheets. And you can also use another function called RIGHT function to extract a substring starting from the right side of the text string. And if you wish to extract a substring starting from the middle position of text string, you can use the MIDB function.

Related Functions

Google Sheets FIND Function
The Google Sheets FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num]) …

September 30, 2021November 1, 2021

Google Sheets FINDB Function

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

Description

The Google Sheets FINDB function returns the position of the first text string (sub string) within another text string counting each double-character as 2.

The FINDB function can be used when you want to get the position of a sub string inside another text string. It will return a number that indicates the starting position of sub string that you are searching in another text string. When searching text string is not found in another text string, the FINDB function will return a #VALUE error in Google sheets.

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

Syntax

The syntax of the FINDB function is as below:

= FINDB(find_text, within_text,[start_num])

Where the FINDB function arguments are:

Find_text -This is a required argument. The text or substring that you want to find. (The string in the Find_text argument can not contain any wildcard characters)
within_text -This is a required argument. The text string that is to be searched for the first occurrence of within_text
start_num-This is an optional argument. It will specify the position in within text string where the search will start. If you omit start_num value, the search will start from the first character of the within_text string, in other words, it is set to be 1 by default. so you can use the google sheets FINDB function to look for the specified text starting from the specified position.

Notes:

The FINDB function will return the position of the first character of find_text in within_text argument.
If there are several occurrences of the find_text within another text string, it only returns the position of the first occurrence.
If the find_text is an empty string, the position of the first character in the within_text is returned.
If the find_text string is not found in within_text, it will return the #VALUE! Error.
If start_number value is not greater than 0, it will return the #VALUE! Error value.
The FINDB function is case-sensitive. It means that uppercase and lowercase letters are different. For example, “find” will not match “FIND”. If you want to ignore case, use the SEARCH function in google sheets.
The FINDB function does not support wildcard characters, so if you want to use wildcard characters to find string, you can use SEARCH function in google sheets.
You need to make sure that Find_text and within_text are not supplied in reverse order, or the #VALUE error will be returned.
You can use the FINDB function in combination with the IFERROR function to check for case when there are not matches within within_text.
You can use the FIND function for standard character sets, and FINDB function for double-byte character sets such as Chinese, Japanese and Korean.

Google Sheets FINDB Function Examples

The below example will show you how to use FINDB function to find the position of a sub string within text string.

#1 To get the position of sub string “习” in cell B1, just using formula:

=FINDB(“习“,A1,1)

When you look for the text “习”” in the Cell B1, it will return 1, which is the position of the first character of find_text “习”” within the Cell B1.

September 29, 2021September 29, 2021

Google Sheets Find Function

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

Description

The Google Sheets FIND function returns the position of the first text string (sub string) within another text string.

The FIND function can be used when you want to get the position of a sub string inside another text string. It will return a number that indicates the starting position of sub string that you are searching in another text string. When searching text string is not found in another text string, the FIND function will return a #VALUE error in Google sheets.

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

Syntax

The syntax of the FIND function is as below:

= FIND(find_text, within_text,[start_num])

Where the FIND function arguments are:

Find_text -This is a required argument. The text or substring that you want to find. (The string in the Find_text argument can not contain any wildcard characters)
within_text -This is a required argument. The text string that is to be searched for the first occurrence of within_text
start_num -This is an optional argument. It will specify the position in within text string where the search will start. If you omit start_num value, the search will start from the first character of the within_text string, in other words, it is set to be 1 by default. so you can use the google sheets Find function to look for the specified text starting from the specified position.

Notes:

The Find function will return the position of the first character of find_text in within_text argument.
If there are several occurrences of the find_text within another text string, it only returns the position of the first occurrence.
If the find_text is an empty string, the position of the first character in the within_text is returned.
If the find_text string is not found in within_text, it will return the #VALUE! Error.
If start_number value is not greater than 0, it will return the #VALUE! Error value.
The FIND function is case-sensitive. It means that uppercase and lowercase letters are different. For example, “find” will not match “FIND”. If you want to ignore case, use the SEARCH function in google sheets.
The FIND function does not support wildcard characters, so if you want to use wildcard characters to find string, you can use SEARCH function in google sheets.
You need to make sure that Find_text and within_text are not supplied in reverse order, or the #VALUE error will be returned.
You can use the FIND function in combination with the IFERROR function to check for case when there are not matches within within_text.

Google Sheets Find Function Examples

The below example will show you how to use FIND function to find the position of a sub string within text string.

#1 To get the position of sub string “google” in cell B1, just using formula:

 =FIND("google",B1,1)

When you look for the text “google” in the Cell B1, it will return 1, which is the position of the first character of find_text “google” within the Cell B1.

#2 To get the position of sub string “google” in cell B1, starting with the ninth character, using formula:

=FIND("google",B1,9)

In the above example, it specified the start_num value as “9“, so you will get the position of the first character “g” of the searched string “google” within the value in Cell B1 when the starting position is 9. it returns 10.

#3 Finding “sheets” string in Cell B1, using the following Find formula:

=FIND("sheets",B1,1)

=FIND("sheets",B1,10)

when you set the starting position as 1, it will return the position of the first “sheets” string in the value of Cell B1.

When you set the starting position as 10, it will return the position of the second “sheets” string in the value of Cell B1. The search will start from the tenth character of the value in Cell B1.

#4 Searching an empty string in Cell B1, using the following Find formula:

=FIND("",B1,1)

In the above example, when you look for an empty string in Cell B1 with the starting position as 1, and it will return the position of the first character of the within_text (the value of Cell B1).

#5 Searching “find” text in Cell B1, using the following Find formula:

=FIND("find", B1,1)

In the above example, when you look for the text “find” in Cell B1 and the string position is 1, it will return a #VALUE! error, As the searched string “find” is not found in Cell B1.

#6 Searching for a text as “google” in Cell B1 and the starting position is a negative number.

=FIND("google",B1,-1)

If the starting position is not greater than 0, it will return a #VALUE! Error.

#7 Searching for a string as “GOOGLE” in Cell B1, using the following formula:

=FIND("GOOGLE",B1,1)

As the FIND function in Google Sheets is case-sensitive, when you look for the string “GOOGLE” in Cell B1, the searched string “GOOGLE” is not found, the function will return a #VALUE! error.

Frequently Asked Questions

Question 1: the FIND function returns a #VALUE error when it does not find the searched text within another string. I want to know if there is another google sheets function in combination with the find function to handle the error message to return the actual value, such as: -1 or throwing others exceptions via returning values, like: -1,0 or FALSE.

Answer: As mentioned above, the FIND function is case-sensitive, and it can be combined with the ISNUMBER function and IF function to create a new IF formula as follows:

=IF(ISNUMBER( FIND("google",B1)), FIND("google",B1), "not found")

The FIND function will locate the position of the searched text “google” within Cell B1, and the ISNUMBER function will check the result returned by the FIND function if it is a number, if TRUE, then return TRUE, otherwise , returns FALSE. then the IF will check the Boolean value returned by ISNUMBER function, If TRUE, then returns the numeric position returned by the second FIND function, If FALSE, returns “not found” string.

If you want a case-insensitive match and you can use the SEARCH function instead of the FIND function in the above formula:

=IF(ISNUMBER( SEARCH("google",B1)), SEARCH("google",B1), "not found")

Question 2: I want to extract a sub string from a string that separated by wildcard characters. I tried to use the below FIND formula to handle it. but it returned a #VALUE error message. Could you please help to check the below formula I used:

=RIGHT(B1,FIND("~**",B1))

Answer: you can use the following formula:

=TRIM(RIGHT(B1,Len(B1) - FIND("**",B1)-1))

The find function will return the position of the first wildcard (**) character within a string in Cell B1. you need subtract the numeric position returned by FIND function from the length of the string in Cell B1 to get the length of sub string to the right of the wildcard character(asterisk). then the RIGHT function extracts the rightmost characters based on the length of sub string.

or you can use another formula to achieve the same result as follows:

=MID(B1,FIND("**",B1)+2,255)

Question 3: I am trying to extract the first word from another text string separated by space character. but it always return a #VALUE error message. I think it should be use the FIND function in combination with another function, such as: left function. but I still don’t know how to combine with those two functions to extract the first word.

Answer: right, you can create a google sheets formula that uses the FIND function and LEFT function. and you can use the below formula:

=IFERROR(LEFT(B1, FIND(" ",B1)-1),B1)

The find function returns the position of the first space character in the text string in Cell B1. the Formula “=FIND(” “,B1)-1 returns the numbers of the first word as the second arguments of the LEFT function.

If the text string in Cell B1 just only contains one word, then the find function will return #VALUE error. to handle this error, you need to use the IFERROR function, if the find function is not find the space character, then return the text string in B1. in other words, it just contains only one word in Cell B1.

Question 4: I am trying to extract an email address from a text string in Cell B1. how to write a google sheets formula to achieve the result.

Answer: you can use a combination of the TRIM function, the LEFT function, the SUBSTITUTE function, the MID function, the MAX function and the REPT function to create a complex google sheets formula to extract email address from a string in Cell B1.

=TRIM(RIGHT(SUBSTITUTE(LEFT(B1,FIND (" ",B1&" ",FIND("@",B1))-1)," ", REPT(" ",LEN(B1))),LEN(B1)))

=TRIM( MID( SUBSTITUTE(B1, " ", REPT(" ",255)), MAX(1, FIND( "@", SUBSTITUTE(B1, " ", REPT(" ",255))) -50),255))

Question 5: I am trying to get the first name from a full name separated by a comma character. how to use a google sheets formula to extract the first name from a name as: “Last name, First name” format.

Answer: you can use a combination with the RIGHT function, the LEN function and the FIND function to create a google sheets formula to get the first name from a name. just using the following formula:

=RIGHT(B1,LEN(B1) - FIND(", ",B1)-1)