The LEFT function in Excel helps you extract characters from the beginning (left side) of a text string. It is commonly used to separate employee IDs, product prefixes, invoice numbers, area codes, and other pieces of information from larger text values.
The LEFT function is especially useful when cleaning data or preparing spreadsheets for analysis. Instead of manually deleting text, you can automatically pull only the characters you need with a simple formula.
In This Guide, You’ll Learn
- What the LEFT function does in Excel
- How to write the LEFT formula correctly
- What each argument means
- Beginner-friendly LEFT examples
- Real-world business uses for the LEFT function
- Common mistakes to avoid
What is the LEFT Function?
The LEFT function returns a specified number of characters from the beginning (left side) of a text string.
This function is commonly used for extracting prefixes, abbreviations, IDs, and codes from larger text values.
LEFT Function Syntax
=LEFT(text, [num_chars])Argument Definitions
- text – The text string you want to extract characters from.
- [num_chars] – Optional. The number of characters you want Excel to return from the left side of the text. If omitted, Excel returns 1 character by default.
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> LEFT Function Data
Basic LEFT Function Example
In this example, we will extract the first 5 characters from a word.
Formulas Used
=LEFT(A2,5)=LEFT(A3,5)=LEFT(A4,5)=LEFT(A5,5)
How This Formula Works
The formula tells Excel to return the first 4 characters from the text in cells A2, A3, A4, and A5.
Extract Employee Department Codes
Many companies use employee IDs where the first few letters identify the department.
Formulas Used
=LEFT(A2,2)=LEFT(A3,3)=LEFT(A4,3)=LEFT(A5,2)
This is useful when categorizing employees by department.
Extract Product Category Prefixes
Many businesses use product SKUs where the first few letters identify a product category.
Formulas Used
=LEFT(A2,4)=LEFT(A3,4)=LEFT(A4,3)=LEFT(A5,4)
The LEFT function helps separate product categories from inventory codes.
Extract State Abbreviations from Locations
Suppose your data contains state abbreviations at the beginning of location names.
Formulas Used
=LEFT(A2,2)=LEFT(A3,2)=LEFT(A4,2)=LEFT(A5,2)
This can help group or filter data by state.
Common LEFT Function Mistakes
Forgetting the Number of Characters
If you leave out num_chars, Excel returns only 1 character.
Using the Wrong Number of Characters
If your codes have different lengths, you may accidentally return incomplete values.
For example:
=LEFT("FIN-2873",2)returns:
FIinstead of FIN.
Forgetting LEFT Works with Text
LEFT treats numbers as text when extracting characters.
Example:
=LEFT(123456,3)returns:
123Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> LEFT Function Data
