Excel LEFT Function
The Excel LEFT Function, a text function, returns certain number of text specified by users. For example, if we write the formula as =LEFT(“AMAZON”, 3), it returns AMA, the first three characters from left. We can see that the LEFT function uses two arguments–the text or the cell references with the text and the number of text we want to extract.
In this section:
- Syntax of LEFT Function.
- Extract the first 2 characters.
- Extract first 6 characters.
- LEFT nested within IF Function
- Combination of FIND and LEFT Function
- LEFT Function with COUNT.
- Extract numbers from LEFT.
- Remove last N characters.
1. Syntax:
LEFT(text, [num_chars]
where:
- text: The text or the cell reference to the text from which users want to extract characters.
- num_chars: [optional] The number of characters to extract, starting on the left side of text. If left, it will return the first character.
2. Extract first 2 characters:
To return the first two characters from left, the formula is =LEFT(C6, 2), which returns the first two characters from left.
3. Extract first 6 characters:
To return the first 6 characters from left, the formula is =LEFT(C9, 6), which returns the first two characters from left.
4. LEFT nested within IF Function:
To categorize based on the left characters or values, we can combine the IF and LEFT function. In our example, we want to categorize the score with the tens digit 9 as Best, and the formula is =IF(LEFT(C6, 1) =”9″, “Best”), which categorize the scores more than or equal to 90 as Best.
5. Combination of FIND and LEFT Function:
To extract first part of the text separated by comma, the formula is =LEFT(C6, FIND(“,”, C6)-1), which extract the last name before comma.
6. LEFT Function with COUNT:
To count the left side number of characters, we can combine the COUNT, IF, and LEFT function. In our example, we want to know how many names started with the letter R. The formula is =COUNT(IF(LEFT(C6:C14, 1)=”R”, 1,””)), which returns the value 4.
7. Extract numbers from LEFT:
To extract numbers from combination of number-text values, the formula is =VALUE(LEFT(C6, 3)), which returns the numbers from left.
8. Remove last N values:
To remove the last N characters from the text string, the formula is =LEFT(C6, LEN(C6)-1), which removes one character (as shown in D6).
More reading:
Some times its a pain in the ass to read what people wrote but this website is really user friendly! .