LEFT in Excel: Syntax, Examples and Errors
Updated 2026-10-14 · Free · Works in Excel & Google Sheets
Syntax
=LEFT(text, [num_chars])Quick answer
LEFT returns characters from the start of a cell: =LEFT(A2, 4) turns "AE-2026-041" into "AE-". The count defaults to 1, asking for more than the cell length just returns the whole text, and the result is always text — even when it looks like a number.
Syntax
=LEFT(text, [num_chars])| Argument | What it means |
|---|---|
| text | The cell or literal holding the text to cut. Required. |
| [num_chars] | How many characters to take from the start: 4 takes the first four. Omit it and LEFT takes exactly one. Must be zero or positive — negative returns #VALUE!. |
Depending on your regional settings, arguments are separated by semicolons instead of commas: =LEFT(A2; 4). This is standard in countries where the comma is the decimal separator.
LEFT counts characters, not bytes and not words: one letter, one digit, one space or one emoji each consume a count. Positions start at 1 — there is no character zero.
Basic examples
=LEFT(A2, 4)The fixed-width workhorse: product codes "AE-2026-041" hand over their country prefix "AE-", phone numbers give up their country dial code, and account numbers expose their branch digits — any time the meaningful part always lives in the first N characters.
=LEFT(A2, SEARCH(" ", A2) - 1)The dynamic upgrade: SEARCH locates the first space and LEFT takes everything before it, so "Jane Doe" yields "Jane" and "Winston Churchill" still yields "Winston" — the cut point follows the data instead of a hard-coded number.
=LEFT(A2, FIND("@", A2) - 1)Extracts the mailbox part of "john.doe@example.com" — "john.doe". Use FIND when case matters (it is case-sensitive); SEARCH when it does not, or when you need wildcards.
=IFERROR(LEFT(A2, SEARCH("-", A2) - 1), A2)The production-grade version: rows without a dash would otherwise explode into #VALUE! because SEARCH finds nothing. Wrapped in IFERROR, those rows simply come back whole instead of breaking the column.
LEFT returns text even when it carves out digits: LEFT(123456, 3) gives the text "123", which will not sum. Wrap numeric extractions in VALUE (=VALUE(LEFT(A2, 3))), and remember dates join as serial numbers — LEFT of a date carves characters out of the serial, not the display.
Common errors and how to fix them
| Symptom | Most common cause | Fix |
|---|---|---|
| #VALUE! | num_chars is negative — or the FIND/SEARCH feeding it cannot locate the delimiter, which itself returns #VALUE!. | Use a positive count, and wrap the delimiter hunt in IFERROR with a fallback, or check the data for missing separators first. |
| Only one character comes back | num_chars was omitted — without it LEFT assumes 1. | Add the count: =LEFT(A2, 4). If you genuinely want one character, writing 1 explicitly documents the intent. |
| The cut lands one character early or late | Off-by-one around the delimiter: to exclude the space you subtract 1; to keep it you subtract 0. | Map it out on one row — SEARCH returns the delimiter position, so SEARCH(...)-1 ends exactly before it. |
| Extracted digits will not calculate | LEFT always returns text, so "007" stays "007" and "123" does not sum. | =VALUE(LEFT(A2, 3)) converts the carved-out digits into a real number (losing leading zeros, which were text anyway). |
| Leading spaces shift the result | The cell starts with invisible padding, so the first characters are spaces, not letters. | Clean first: =LEFT(TRIM(A2), 4), or strip whitespace on the whole column in one pass. |
Not an error, but a classic surprise: asking LEFT for more characters than the cell holds returns the entire cell, not an error. =LEFT("Hi", 50) is just "Hi" — handy for padding-free safe cuts, confusing when you expected a warning.
LEFT vs. MID
| LEFT | MID | |
|---|---|---|
| Starts cutting from | Always the first character | Any position you specify: =MID(A2, 5, 3) |
| Arguments | Text and a count | Text, a start position and a count |
| Best for | Prefixes, first names, fixed-width headers | Middle segments — area codes, the part between two delimiters |
| Dynamic start | Only the end can move (via FIND/SEARCH) | Both ends can move — FIND feeds the start position |
LEFT is a specialized MID with a permanent start of 1: =LEFT(A2, 4) is exactly =MID(A2, 1, 4). When the segment can sit anywhere but the beginning, move to MID — the two functions nest together constantly in parsing formulas.
LEFT vs. Text to Columns and TEXTBEFORE
| LEFT (+ FIND/SEARCH) | Text to Columns | TEXTBEFORE (Microsoft 365) | |
|---|---|---|---|
| Output | A live formula in a new column; source untouched | Splits the cells in place, overwriting neighbors if you are not careful | A live formula: =TEXTBEFORE(A2, "-") |
| Delimiter logic | Hand-built with FIND/SEARCH and -1 offsets | Dialog-driven, handles repeated delimiters visually | One argument — no FIND, no minus-one math |
| Survives new rows | Yes — fill down and it recalculates | No — rerun on the new data | Yes — dynamic by design |
| Available in | Every Excel version and Google Sheets | Every Excel version | Excel 365 / 2021 and later only |
Choose by lifespan: LEFT formulas for parsing that must survive repeated imports and refreshes, Text to Columns for a one-time reshaping of a finished table, TEXTBEFORE when your Excel is modern enough and the only thing you need is "everything before the delimiter".
LEFT in Google Sheets
- Identical syntax and defaults — every formula above pastes into Sheets unchanged.
- Comma-decimal locales use semicolons: =LEFT(A2; 4), exactly like Excel.
- FIND stays case-sensitive and SEARCH stays case-insensitive with wildcard support — the pairing behaves the same.
- For pattern-based prefixes Sheets adds REGEXEXTRACT, which can pull "everything before the first digit" in one expression instead of nesting FIND calls.
Limitations and pro tips
- One cut per call: splitting a code into three pieces means three columns — LEFT, MID and RIGHT side by side is the classic parsing layout.
- FIND vs. SEARCH is a deliberate choice: SEARCH for case-insensitive delimiters like a space or dash, FIND when "a" and "A" must mean different targets.
- Wrap every delimiter-driven extraction in IFERROR — a single row missing its separator otherwise spreads #VALUE! through the whole column.
- Numbers stay text after the cut: =VALUE(LEFT(A2, 3)) is the way back to a calculable number.
- For the text before the nth delimiter, nest SEARCH: SEARCH("-", A2, SEARCH("-", A2) + 1) finds the second dash, and LEFT cuts up to it.
Think of LEFT as the first station of a parsing line: TRIM the padding, locate the delimiter with SEARCH, carve with LEFT, convert with VALUE when the result is numeric. Each stage stays in its own column, so when the import format shifts next month, only one formula changes.
Related Tools
Related Functions
Frequently Asked Questions
What does the LEFT function do in Excel?
It returns a chosen number of characters from the beginning of a cell: =LEFT(A2, 4) takes the first four. With no count it takes one character, and pairing it with FIND or SEARCH lets the cut point move with delimiters like spaces or dashes.
How do I extract text before a space or dash with LEFT?
Locate the delimiter and subtract one: =LEFT(A2, SEARCH(" ", A2) - 1) takes everything before the first space. SEARCH ignores case, FIND respects it, and IFERROR around the whole thing keeps rows without the delimiter intact.
Why does LEFT return #VALUE!?
Either the character count is negative, or an embedded FIND/SEARCH located no delimiter — FIND returns #VALUE! when the text is missing, and LEFT inherits it. Use IFERROR with a fallback and check the rows for missing separators.
What is the difference between LEFT and MID?
LEFT always starts at character 1 and needs only a count; MID starts wherever you say and needs both a start position and a count. LEFT is ideal for prefixes and first names; MID handles segments buried in the middle.
Why do the numbers extracted with LEFT not calculate?
LEFT returns text, so its "123" is a label, not a value. Convert the result with =VALUE(LEFT(A2, 3)) — but note that leading zeros disappear in a real number, since they only existed as text.
What happens when num_chars is larger than the text?
LEFT simply returns the entire cell — =LEFT("Hi", 50) is "Hi", with no error and no padding added. The same rule makes oversized counts safe for "take up to N characters" behavior.
Does LEFT work in Google Sheets?
Yes — same name, same arguments, same FIND/SEARCH pairing and semicolon convention in comma-decimal locales. Sheets additionally offers REGEXEXTRACT for pattern-based extraction.
Ready to try it on your own file?
Open Split Text into Columns in Excel — It's Free