When working with text data in Excel, extracting specific parts of a string can be essential. One of the most useful functions for this is LEFT. It’s simple but powerful, allowing you to grab a certain number of characters from the beginning of a string. In this article, I’ll explain exactly how LEFT works in Excel and provide some of the best examples of its use.
What is the LEFT function in Excel?
The LEFT function in Excel extracts a specified number of characters from the start of a given text string. It is incredibly useful for parsing structured data, such as extracting country codes, first names, or prefixes.
LEFT function syntax
The syntax of the LEFT function is straightforward:
=LEFT(text, num_chars)- text: The string from which you want to extract characters.
- num_chars: The number of characters to extract, starting from the left.
The num_chars parameter is optional. If omitted, Excel assumes a value of 1 and extracts just the first character.
Basic example of the LEFT function
Let’s take a look at a simple example. Suppose you have the following text in cell A1:
Excel FunctionsIf you use the formula:
=LEFT(A1, 5)This will return:
ExcelUsing LEFT with numbers
The LEFT function treats numbers as text if they are entered as text. However, if applied to actual numerical values, it may return unexpected results.
Consider the number 123456 in cell A1. If you apply this formula:
=LEFT(A1, 3)You might expect the result to be 123, but if A1 contains a genuine number (not text), LEFT will return numbers as text. This can cause issues in calculations.
To convert the result back into a number, use:
=VALUE(LEFT(A1, 3))Practical applications of LEFT in Excel
1. Extracting first names from full names
If you have a column of full names and you need to extract the first name, LEFT can help when combined with SEARCH.
Example: If A1 contains John Doe, use:
=LEFT(A1, SEARCH(" ", A1) - 1)This formula finds the position of the first space and extracts the first part of the name.
2. Extracting country codes from phone numbers
Suppose you have international phone numbers starting with a country code, like +44 7890 123456 in A1. You can extract the country code using:
=LEFT(A1, SEARCH(" ", A1) - 1)This retrieves +44, assuming the country code is separated by a space.
3. Working with SKU or product codes
When product codes contain meaningful prefixes, you can extract them to categorize products. If A1 contains SKU-12345, you could get the “SKU” prefix using:
=LEFT(A1, SEARCH("-", A1) - 1)Common errors when using LEFT
| Error | Cause | Solution |
|---|---|---|
| #VALUE! | num_chars is negative | Ensure num_chars is a positive number |
| Incorrect output | Applied on numbers, returning them as text | Wrap result in VALUE() to convert back to a number |
| Empty result | Using a SEARCH function that doesn’t find the delimiter | Check if searched text actually exists within the string |
Combining LEFT with other functions
LEFT with FIND or SEARCH
Sometimes, you need LEFT to return only part of a text string up to the first occurrence of a certain character. The SEARCH (or FIND, which is case-sensitive) function helps with this.
=LEFT(A1, SEARCH("-", A1) - 1)LEFT with LEN for extracting variable-length text
If you need to extract a variable amount of text from the left but exclude a specific number of characters at the end, you can use LEN.
=LEFT(A1, LEN(A1) - 3)This extracts everything except the last three characters.
Conclusion
The LEFT function in Excel is invaluable for handling text. Whether you’re dealing with names, product codes, or phone numbers, this function simplifies extracting data efficiently. By combining it with other functions like SEARCH and LEN, you can handle more complex text operations with ease.
Other interesting article:
How TEXT works in Excel? Best TEXT examples