What Is LEFT Function In Google Sheets?
The LEFT Function in Google Sheets is a text function that returns the number of characters from the beginning of the string, i.e., from the left.
With the Google Sheets LEFT function we can extract the data, but also use it along with other functions to perform complex computations.
For example, consider cell A2 with the text string. We will use the LEFT in Google Sheets to extract the required character.
Select cell B2, enter the formula =LEFT(A2) and press “Enter”, as shown below.
The output is shown above. The first letter is extracted from the selected string, by default, when we so not provide any other entry. However, when the next argument value is entered, then the formula extracts the data from the specified value.
Key Takeaways
- The LEFT Function in Google Sheets is a function that fetches a substring from a string, starting from the left side with the optional argument provided or not.
- The cell values can be anything whether textual, numeric values, symbols, date values, etc.
- We can provide any value from 0 or more that indicates the number of characters to display but not a negative number because, the #VALUE! error occurs if the given value is less than 0.
- If there are any extra spaces in the formula, the obtained value might have an error or the result might be incorrect.
- We can use the LEFT() with other functions, such as SEARCH(), CONCATENATION(), LEN(), IF(), etc, for better computations.
Syntax
The syntax of the LEFT formula in Google Sheets is,
The arguments of the LEFT formula in Google Sheets are,
- string: It is a mandatory argument and denotes the text string from which we must extract the characters.
- [number_of_characters]: It is an optional argument indicating the number of characters we want to fetch and display.
How To Use LEFT Function In Google Sheets?
We can use the LEFT function in Google Sheets in two ways, as follows:
- Access from the Google Sheets ribbon.
- Enter the formula in the worksheet manually.
Method #1 – Access From the Google Sheets Ribbon →
Step 1: Choose an empty cell for the output → select the “Insert” tab → click the “Function” option right arrow → click the “Text” option right arrow → select the “LEFT” function, as shown below.
Step 2: The “LEFT” formulaappears, as shown below. Enter the argument as cell reference.
Method #2 – Enter the Formula in the Worksheet Manually →
Step 1: Select an empty cell for the output.
Step 2: Type =LEFT( in the cell, as shown below. [Alternatively, type =L or =LE and double-click the LEFT from the Google Sheets suggestions.]
Step 3: Enter the arguments as cell values or cell references and close the brackets.
Step 4: Press Enter to view the outcome.
Examples
Let us consider some LEFT function in Google Sheets examples with to extract substrings, along with other functions, such as CONCATENATE, IF, SEARCH, etc.
Example #1 – Extracting A Substring Before A Certain Character/Symbol
Consider the dataset given below that consists of strings and we will be Extracting A Substring Before A Certain Character/Symbol given in column B using the LEFT() and the SEARCH() functions.
The steps to extract substring before the selected character or symbol are,
Step 1: Select cell C2 and enter the formula =LEFT(A2,SEARCH(“B2”,A2)), as shown below.
Step 2: Press “Enter” and Google Sheets will give us an autofill option, as shown below. We can either choose the autofill or use the fill handle method.
Step 3: Drag the formula from cell C2 to C6 using the fill handle, to get the following results.
The output is as above, i.e. the formula searches for the selected character/symbol first and then displays the text from the start till the symbol, including it too.
Example #2 – Removing the Last N Characters From a String.
We have list or fruits in their plural form in the dataset given below. We will be Removing the last N characters from a String using the LEFT() and the LEN() functions.
The steps to remove a few letters from last of the strings are,
Step 1: Select cell B2. Now, enter the formula =LEFT(A2,LEN(A2)-1), as shown below.
Step 2: Press “Enter.” Then, drag the formula from cell B2 to B6 using the fill handle, to get the following results.
In the output above, it removes the the last characters indicating that the plural data is now singular.
Example #3 – LEFT and CONCATENATE.
Consider the dataset of the famous sitcom actors first names and the last names. We will combine the name and extract the required character using the LEFT and the CONCATENATE functions. We have retrieved the CONCATENATE results in column C for our reference.
The steps to combine LEFT and CONCATENATE are,
Step 1: Select cell D2 and enter the formula =LEFT(CONCATENATE(A2,B2)), as shown below.
Step 2: Press “Enter.” Then, drag the formula from cell D2 to D6 using the fill handle, to get the following results.
Example #4 – LEFT and IF.
We have the dataset of laptop models. We will identify the Dell models using the LEFT andthe IF functions.
The steps to combine LEFT and IF are,
Step 1: Select cell B2 and enter the formula =IF(LEFT(A2,4)=”Dell”,”DELL”,””), as shown below.
Step 2: Press “Enter” and drag the formula from cell B2 to B9 using the fill handle, to get the following results.
Important Things To Note
- The LEFT Function in Google Sheets formula returns a value that can be a string or text. One uses it to extract digits from numbers as well.
- The number_of_characters argument, is optional, and if not entered, by default, is 1. If we enter 0, the we will get a blank result, indicating the space before the selected string.
- If the optional argument is a negative value, i.e., less than 0, then we will get the #Value! error.
- When the data is copied from other sources, sometimes the leading spaces may be left, which returns the wrong output or errors. For instance, assume that if we copy the text in cell A1 by mistake, it also copies some leading spaces. Then, when we apply the LEFT Function, the function will either return an error or displays incorrect output.
Frequently Asked Questions (FAQs)
A few reasons the LEFT function in Google Sheets may not work are,
a. The cell range selected or the referenced cell value is modified or deleted.
b. The cell value for the arguments given directly are not inserted with double-quotes. If we enter numeric values within quotes, it will be considered as a textual value.
c. We have entered the optional value as 0, negative number or value more than the string length.
d. The character or symbol before which to extract is not provided.
e. If the result of a formula is an error and it is an argument value for the formula.
We often forget in which category a function falls, here, the “LEFT” function. Then, we can insert the function as follows:
Choose an empty cell → select the “Insert” tab → click the “Function” option right arrow → click the “All” option right arrow → select the “LEFT” function, as shown below.
However, as always, entering the function manually is the best way to avoid confusion.
Alternatively, we can find the Functions icon to insert the LEFT Google Sheets Function by following the path shown below.
• Choose an empty cell → click the “More” option represented by the three vertical dots at the end of the toolbar, as shown below.
• A list of icons appears when we click the “More” option. Here, click the “Functions” icon, as shown below.
• Here, click the “Functions” option → click the “All” option right arrow → select the “LEFT” function, as shown below.
We can insert the Google Sheets IF function as follows:
Choose an empty cell for the output → select the “Insert” tab → click the “Function” option right arrow → click the “Logical” option right arrow → select the “IF” function, as shown below.
We can insert the Google Sheets CONCATENATE function as follows:
Choose an empty cell for the output → select the “Insert” tab → click the “Function” option right arrow → click the “Text” option right arrow → select the “CONCATENATE” function, as shown below.
We can insert the Google Sheets SEARCH function as follows:
Choose an empty cell for the output → select the “Insert” tab → click the “Function” option right arrow → click the “Text” option right arrow → select the “SEARCH” function, as shown below.
Download Template
This article must be helpful to understand LEFT Function in Google Sheets, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What is LEFT Function in Google Sheets. We learn its syntax & how to use it to retrieve values from the left of a string. You can learn more from the following articles. –
Leave a Reply