LEFT Function in Excel and Google Sheets
Purpose
Extract a specified number of characters from the beginning of a text string
Return value
A text string containing the leftmost num_chars characters of the text argument
LEFT is the go-to function for extracting the first part of a text value — a prefix, a code segment, a country code, the first few digits of an identifier. I use it constantly in data-cleaning work where structured text fields need to be split into components. Product codes like PRD-001 where the first three characters identify the category. Phone numbers where the first two digits are the country code. Post codes where the first two letters indicate the area. When the number of characters to extract is always the same, LEFT takes two arguments and that is the whole formula. When the prefix length varies, combining LEFT with FIND to locate a delimiter gives you a dynamic extract that adapts to each row automatically. LEFT is one of three text-extraction functions — alongside RIGHT and MID — that together cover any substring extraction requirement you will encounter.
Syntax
✓ Excel 2003+
✓ Google Sheets
= Same syntax
=LEFT(text, [num_chars])
num_chars defaults to 1 if omitted.
Arguments
| Argument | Required | Description |
|---|---|---|
| text | Required | The text string to extract from. Can be a cell reference, a literal string in quotes, or a formula result. Numbers are treated as text. |
| num_chars | Optional | The number of characters to extract from the left, starting at position 1. Must be zero or positive — negative values cause #VALUE!. Defaults to 1 if omitted. If larger than the string length, the entire string is returned. |
How it works
LEFT counts characters from the first position in the text string and returns the requested number. Position 1 is the first character on the left. Spaces, hyphens, and special characters all count as one character each. The result is always a text string — even if the extracted characters look like a number, the result is text. Wrap in VALUE if you need the output to be numeric for calculations. When the number of characters to extract is fixed — always 3 for a category code, always 2 for a country prefix — LEFT is a simple two-argument formula. When the prefix length varies because it ends at a delimiter, combine LEFT with FIND: =LEFT(A2,FIND("-",A2)-1) extracts everything before the first hyphen. The -1 excludes the delimiter itself. RIGHT extracts from the end of the string and MID extracts from any position in the middle — the three functions together handle all fixed-position text extraction needs.
Examples
1
Extract the 3-character category prefix from each product code in a structured code column.
| A | B | |
|---|---|---|
| 1 | Product Code | Category |
| 2 | PRD-001 | =LEFT(A2,3) |
| 3 | PRD-002 | PRD |
| 4 | CAT-005 | CAT |
| 5 | REG-012 | REG |
| 6 | CAT-008 | CAT |
| 7 | PRD-099 | PRD |
Row 2: PRD
— PRD-001 has PRD as its first three characters — LEFT returns PRD.
The num_chars argument is 3 because every code in this dataset has a fixed 3-character prefix. The hyphen at position 4 and the digits after it are ignored. This is the simplest and most common LEFT usage.
2
Extract the variable-length text before the hyphen in each code using LEFT combined with FIND.
| A | B | |
|---|---|---|
| 1 | Code | Prefix |
| 2 | NORTH-001 | =LEFT(A2,FIND("-",A2)-1) |
| 3 | SE-045 | SE |
| 4 | CENTRAL-012 | CENTRAL |
| 5 | NW-088 | NW |
| 6 | EAST-003 | EAST |
Row 2: NORTH
— FIND locates the hyphen in NORTH-001 at position 6. LEFT(A2,6-1) returns the first 5 characters: NORTH.
FIND returns the position of the hyphen. Subtracting 1 excludes the hyphen itself. The result adapts automatically to each row — SE extracts 2 characters, CENTRAL extracts 7 — without any manual adjustment.
3
Extract the first 4 characters of a numeric ID and convert the result to a number using VALUE so it can be used in calculations.
| A | B | |
|---|---|---|
| 1 | ID | First 4 (numeric) |
| 2 | 20241501 | =VALUE(LEFT(A2,4)) |
| 3 | 20230802 | 2023 |
| 4 | 20241203 | 2024 |
| 5 | 20220101 | 2022 |
| 6 | 20251115 | 2025 |
Row 2: 2024
— LEFT extracts 2024 as a text string from 20241501. VALUE converts it to the number 2024 so it can be compared to years, used in arithmetic, or passed to date functions.
Without VALUE, the result 2024 is text and comparisons like =B2>2023 would fail. Wrapping in VALUE is the standard pattern whenever LEFT results need to participate in numeric operations.
Common use cases
1. Extract a fixed-length category or type code from a structured product identifier
=LEFT(A2,3)
2. Get the variable-length text before a delimiter like a hyphen, slash, or space
=LEFT(A2,FIND("-",A2)-1)
3. Extract the year from an 8-digit date stored as text such as 20241501
=LEFT(A2,4)
4. Get the first two characters of a postal code to identify the delivery region
=LEFT(A2,2)
5. Extract a prefix and convert it to a number for use in calculations or comparisons
=VALUE(LEFT(A2,4))
Common errors
#VALUE! error
num_chars is negative, or FIND inside the LEFT formula cannot locate the delimiter and returns #VALUE! which propagates through LEFT.
Fix: Ensure num_chars is zero or positive. Wrap the FIND in IFERROR if the delimiter may not always be present: =IFERROR(LEFT(A2,FIND("-",A2)-1),A2).
Result is text when you need a number
LEFT always returns a text string even when the extracted characters look numeric. Any comparison or arithmetic on the result will treat it as text.
Fix: Wrap in VALUE: =VALUE(LEFT(A2,3)) converts the text result to a real number for calculations.
Wrong characters extracted
The text contains leading spaces which shift all character positions right. FIND or fixed num_chars values are then off by the number of leading spaces.
Fix: Wrap the text argument in TRIM: =LEFT(TRIM(A2),3) removes leading and trailing spaces before extracting.
Tips and variations
Combine LEFT with FIND for variable-length prefix extraction
When the prefix ends at a delimiter rather than at a fixed position, FIND locates the delimiter and LEFT extracts up to it. This dynamic combination adapts to every row without manual counting.
=LEFT(A2,FIND("-",A2)-1)
Wrap in VALUE when the result needs to be numeric
LEFT always returns text. If the extracted characters will be used in arithmetic, date functions, or numeric comparisons, add VALUE around the LEFT formula. This is the most common extra step in extraction formulas.
=VALUE(LEFT(A2,4))
Use TRIM before LEFT on imported data
Data from external sources often has invisible leading spaces. TRIM removes them before LEFT counts positions, ensuring the correct characters are extracted. Skipping TRIM on imported data is one of the most frequent causes of wrong LEFT results I diagnose.
=LEFT(TRIM(A2),3)
Excel vs Google Sheets
Excel vs Google Sheets
LEFT works identically in Excel 2003 and newer and every version of Google Sheets. The syntax, character counting, and behaviour when num_chars exceeds the string length are the same in both applications. Formulas copy between them without changes.
Related Functions
Frequently asked questions
LEFT extracts a specified number of characters from the start (left side) of a text string. =LEFT("Hello",3) returns Hel. It is most commonly used to extract codes, prefixes, or the first few characters of a structured text field like a product code, postal code, or identifier.
When the number of characters to extract varies by row, use FIND to locate a delimiter and LEFT to extract up to that point. =LEFT(A2,FIND("-",A2)-1) extracts everything before the first hyphen in A2. Subtract 1 from the FIND result to exclude the delimiter itself from the extract.
LEFT extracts characters from the start of the string. RIGHT extracts from the end. MID extracts from any position in the middle — you specify the start position and how many characters to take. Use LEFT for prefixes, RIGHT for suffixes and endings, and MID for anything in between.
LEFT treats its input as text. If A2 contains the number 12345, =LEFT(A2,3) returns the text string 123, not the number 123. If you need the result as a number for calculations, wrap it in VALUE: =VALUE(LEFT(A2,3)).
LEFT returns the entire string without error. =LEFT("Hello",100) returns Hello — the full 5-character string — because LEFT simply stops at the end of the string rather than padding with spaces or erroring. This makes LEFT safe to use with a large num_chars as an overcount when you want everything from the start.