This guide covers everything you need to know about the Google Sheets LEFT function, including its definition, syntax, use cases, and how to use it.
What is the LEFT Function? How Does It Work?
The LEFT function in Google Sheets lets you grab certain letters from the beginning of a word or sentence. It’s super handy if you just want a part of the text.
Here’s how it works: you tell the function which text to look at and how many letters you want from the start. If you don’t say how many letters, it’ll just give you the first one. If you say zero letters, you’ll get nothing back.
For example, if you type LEFT(“Hello World”, 5), you’ll get “Hello”. But if you just type LEFT(“lorem ipsum”), you’ll get “l” because you didn’t specify how many letters.
In short, the LEFT function helps you take pieces from the start of any text in Google Sheets. It’s a neat trick to manage your data better.
LEFT Syntax
The syntax and arguments for the function are as follows:
LEFT(string, [number_of_characters])
Here, there are two arguments:
- string: This is the word or sentence you want to take letters from. You can either type it directly or point to a cell that has the text.
- number_of_characters: This part is optional. If you tell it a number, it’ll grab that many letters from the start of the text. If you skip this part, it’ll just give you the very first letter of the text.
Usage notes related to the syntax and arguments:
- The number_of_characters argument should be a non-negative integer. If a negative number is provided, the function will return an error.
- If the number_of_characters argument is larger than the length of the string, the function will return the entire string.
- 0 is a valid input for number_of_characters. If 0 is provided, the LEFT function will return an empty string.
- If the string argument is not a string data type, the function will attempt to convert it into one. For example, if a number is provided, it will be treated as a string.
- The string argument cannot be an array or a range of cells. The function will only consider the first cell in the range or array.
Examples of How to Use the LEFT Function
Here are some practical examples of how to use the LEFT function in Google Sheets.
Example #1: Extracting the First Letter from a Name
Suppose you have the name “James” and you want to get just the first letter. You’d use the LEFT function like this:
=LEFT(“James”, 1)
With this setup, the function will give you “J” as the result.
Example #2: Extracting Area Code from a Phone Number
If you have a phone number like “(123) 456-7890” and you want to get just the area code, you’d use the LEFT function like this:
=LEFT(“(123) 456-7890”, 5)
With this, the function will give you “(123)” as the result.
Example #3: Extracting the Year from a Date
If you have a date like “2022-01-01” and you want to get just the year, you’d use the LEFT function this way:
=LEFT(“2022-01-01”, 4)
With this setup, the function will give you “2022” as the result.
Example #4: Extracting Domain from an Email Address
If you have an email address like “john.doe@gmail.com” and you want to get the part before the “@”, you’d combine the LEFT and FIND functions like this:
=LEFT(“john.doe@gmail.com”, FIND(“@”, “john.doe@gmail.com”) – 1)
With this formula, you’ll get “john.doe” as the result.
Why Is LEFT Not Working? Troubleshooting Common Errors
If you are experiencing issues while using the LEFT function in Google Sheets, it could be due to several common errors. Understanding these errors, their causes, and how to resolve them can help you use the LEFT function more effectively.
#VALUE! Error
Cause: The #VALUE! error typically occurs when the syntax of the LEFT function is incorrect. For example, this error will appear if the text argument is missing or if the number of characters to extract is non-numeric.
Solution: To fix this, ensure that your syntax is correct. The syntax for the LEFT function is LEFT(text, [num_characters]).
#REF! Error
Cause: The #REF! error appears when the cell reference is not valid. This can happen if the cell you’re referring to has been deleted.
Solution: To resolve this, double-check your cell references to ensure they are valid. If you’ve deleted a cell, you might need to undo the deletion or adjust your formula to refer to the correct cell.
#NUM! Error
Cause: This error is displayed when the number of characters specified to be returned by the LEFT function is less than zero.
Solution: To fix the #NUM! error, ensure that the number of characters you want to extract is zero or a positive number. The LEFT function cannot extract a negative number of characters.
#NAME? Error
Cause: The #NAME? error appears when Google Sheets does not recognize the text in the formula. This could happen if you typed the function’s name incorrectly.
Solution: To resolve this, check your formula to ensure the function’s name is correctly spelled. The correct spelling is LEFT.
Using LEFT With Other Google Sheets Functions
Combining the LEFT function with other Google Sheets functions can create powerful formulas to manipulate and analyze data. Here are some examples of how to use LEFT with other functions:
With FIND
Usage: The FIND function is used to find the position of a specific character or substring in a string. When combined with the LEFT function, you can extract a certain part of a string.
Example: Suppose you have a list of product codes in column B and want to extract the part before the first hyphen. You can use the combination of LEFT and FIND functions.
=LEFT(B2, FIND(“-“,B2)-1)
This formula will find the position of the first hyphen in the product code and subtract 1 to get the position of the last character before the hyphen. Then the LEFT function will extract that many characters from the beginning of the product code.
With CONCATENATE
Usage: The CONCATENATE function combines two or more text strings into one text string. When combined with the LEFT function, you can create new text strings based on specific parts of existing ones.
Example: Suppose you have a list of full names in column C and want to create usernames for each person using the first three letters of their first name and the first three letters of their last name. You can use a combination of LEFT and CONCATENATE functions.
=CONCATENATE(LEFT(SPLIT(C2,” “),3), LEFT(SPLIT(C2,” “,FALSE,2),3))
This formula will split the full name into first and last name, extract the first three letters from each, and then combine them into one text string.
For more details on the LEFT function, check out the official documentation at the Google Docs Editors Help Center.
