Excel LEFT Function (6 Practical Examples)

Excel LEFT Function

The excel LEFT Function returns the first character or characters in a text string based on the number of characters users specify. On the other hand, LEFTB returns the first character or characters in a text string, based on the number of bytes users specify. The Excel LEFT function is used to extract a given number of characters from the left side of a supplied text string.

In this section:

---

1. Syntax of LEFT Function
2. Extract specific number of characters from left
3. Extract text or value before space
4. Extract text before second space or comma or other separator
5. Extract text or value before first hyphen
6. Extract text before specific characters with LEFT/SEARCH
7. Force LEFT function to return a number

---

1. Syntax of LEFT Function:

LEFT(text, [num_chars])

where:

• Text: The text string containing the characters users want to extract. REQUIRED.
• Num_chars: Number of characters users want to extract with LEFT.
  • Num_chars must be greater than zero.
  • LEFT returns all of the text if num_chars is greater than the length of text.
  • If the num_chars is omitted, LEFT assumes it as 1 and returns the first letter or value.

2. Extract specific number of characters from left:

To extract specific number of text from left, the formula is =LEFT(C6, Num_chars), which returns the number of characters specified by users. If Num_chars=2, LEFT returns the first two characters.

3. Extract text or value before first space:

To extract text or value before first space, combine the LEFT and FIND function and the formula is =LEFT(C6, FIND(” “, C6)), which returns the first characters before first space.

4. Extract text before second space or comma:

To extract text before the second space or comma, the formula is =IF(ISERROR(FIND(” “,C6,FIND(” “,C6,1)+1)),C6,LEFT(C6,FIND(” “,C6,FIND(” “,C6,1)+1))), which returns the text before the second space.

Note: If users want to extract before second comma or other separators, they just need to change the space with COMMA or SEPARATOR.

5. Extract text before first hyphen (-):

To extract text before the first hyphen, the formula is =LEFT(C6, FIND(“-“, C6)-1), which returns the text before the first hyphen. Users can use the same formula to extract text before the first comma or other separator, and they just need to replace hyphen (-) with the separator.

6. Extract text before specific separator with LEFT and SEARCH:

To extract text before specific characters, the formula is (in combination with SEARCH) =

7. Force LEFT to return number:

To force LEFT function to return the numbers only, the formula is =VALUE(LEFT(C6, 3)), which returns the first three numbers only.

More related readings:

1. MS Office Post of LEFT Function
2. How to extract first names from list of names
3. Excel FIND Function
4. Excel FIXED Function
5. Excel ARRAYTOTEXT Function
6. Excel CLEAN Function with practical examples
7. Excel CONCAT Function to join text
8. Excel REPLACE Function to replace text
9. Excel TRIM Function with Practical Examples