The LEFT and RIGHT functions help extract substrings from the start or end of a specified string in Google Sheets.
These functions are useful for regular tasks such as separating first and last names when combined with SEARCH or FIND.
Let’s learn about these two text functions one by one and their combined use with SEARCH and FIND.
LEFT Function in Google Sheets
Syntax:
LEFT(string, [number_of_characters])Arguments:
string: The string from which to extract the left portion at the specifiednumber_of_characters.number_of_characters: The number of characters to return. The default value is 1. If you specify 0, the formula will return an empty string.
Examples:
=LEFT("Info Inspired", 4) // returns "Info"
=LEFT("Info Inspired", 0) // returns ""Combined Use of LEFT and SEARCH or FIND
The SEARCH and FIND functions are useful for finding the position of a specific character or substring in a text. The former is case-insensitive, whereas the latter is case-sensitive.
Below, I’ll demonstrate the combination of the LEFT and SEARCH functions in Google Sheets. Remember, you can replace SEARCH with FIND.
Assume you want to extract the first name from cell A1. You need to search for the first space character in cell A1 and use that position as the number of characters to return in LEFT.
=LEFT(A1, SEARCH(" ", A1)-1)If cell A1 contains “Ashley Holmes”, the formula will return the first name “Ashley”. The formula subtracts 1 from the SEARCH position to avoid including the space character in the result.
RIGHT Function in Google Sheets
Syntax:
RIGHT(string, [number_of_characters])Arguments:
string: The string from which to extract the right portion.number_of_characters: The number of characters to extract, 1 by default. If you specify 0, the formula will return an empty string.
Examples:
=RIGHT("Info Inspired", 8) // returns "Inspired"
=RIGHT("Info Inspired", 0) // returns ""Combined Use of RIGHT, LEN, and SEARCH or FIND
You can use a combination of LEN and SEARCH or FIND functions with the RIGHT function to dynamically extract a substring from a string.
For example, to extract the last name from the name in cell A1, use the following formula:
=RIGHT(A1, LEN(A1)-SEARCH(" ", A1))Where:
string:A1number_of_characters:LEN(A1) - SEARCH(" ", A1)
In number_of_characters, the SEARCH function returns the position of the first space character in the text. When you subtract this position from the length of the string, you get the length of the substring to extract.
If cell A1 contains “Ashley Holmes”, the formula will return “Holmes”.
Note: For advanced text extraction, use the REGEXEXTRACT function.
Hello,
Is there a way to use the left, right function with the Query function?
I’m trying to take
YYYYMMDDin the B column, number format intoYYYY-MM-DDas date format by query with text function…Thank you!
Hi, Jun,
That’s possible. I could try if you share a demo/sample sheet.
Hello!
I’m sharing the link.
— Link removed by admin —
Thank you!
Hi, Jun,
Give it a try.
=ArrayFormula(QUERY({A2:L19,date(left(M2,4),mid(M2:M19,5,2),RIGHT(M2:M19,2)),N2:BE19},
"select Col6,Col16,Col13,Col10,Col18,Col57,Col14 where Col16 is not null"))
Thank you so much!
The best I have ever seen.