The Excel LEFT function, explained interactively.
LEFT returns the first num_chars characters of a text value. Simple signature, one quiet superpower: LEFT never errors on an over-long num_chars — it just caps at the text’s length. Pair it with FIND and you have a dynamic splitter.
How to use LEFT
- Type
=LEFT(and supply the text — a cell reference, a literal string, or a formula that returns text. - Add a comma and num_chars — how many characters to take. Defaults to
1when omitted; anything ≥ the text length returns the whole string. - Close the parenthesis. LEFT returns text — wrap with
VALUE()if you need a number for arithmetic. - For dynamic splits (before a dash, space, @ sign), combine with
FIND:=LEFT(A2, FIND("-", A2) - 1).
Move the demo’s num_chars through its presets — 3 picks the country prefix from the SKUs, 10 pulls the ISO date out of a full timestamp.
1 when omitted. 0 returns an empty string; any value ≥ the text length returns the full text, no error.| A | B | |
|---|---|---|
| 1 | Source | LEFT (N=3) |
| 2 | NYC-12045 | NYC |
| 3 | LON-00371 | LON |
| 4 | TOK-99110 | TOK |
| 5 | +1-415-555-2041 | +1- |
| 6 | 2026-04-19T13:25:00Z | 202 |
| 7 | ava@formulagym.com | ava |
=LEFT(A2, 3). num_chars = 3: LEFT returns the first 3 characters of each cell. Other B cells show what B3:B7 would return if you filled the formula down.LEFT syntax and arguments
One required argument, one optional. See Microsoft’s official LEFT / LEFTB reference for the canonical specification.
A2), literal ("hello"), or any formula that resolves to text. Numeric inputs are coerced to text via General formatting before extraction.1 when omitted. 0 returns an empty string. Any value ≥ the text length returns the entire string — LEFT silently clamps, never errors. Negative values return #VALUE!.LEFT examples
Four patterns that cover nearly every real-world LEFT use.
Example 1: LEFT for a fixed prefix
The canonical case — SKUs and part numbers use a fixed-length prefix.
Returns NYC from NYC-12045. Works uniformly as long as the prefix length is fixed. If the prefix length varies, use the FIND pattern below instead.
Example 2: LEFT combined with FIND — dynamic split
Return everything before a delimiter, regardless of how long the prefix is.
FIND returns the position of the dash; the -1 drops the delimiter itself. If the delimiter might be missing, wrap with IFERROR: =IFERROR(LEFT(A2, FIND("-", A2) - 1), A2).
Example 3: LEFT to extract the year from a timestamp
ISO timestamps (2026-04-19T13:25:00Z) put the year first, so LEFT(..., 4) extracts it cleanly.
Wrap with VALUE to convert the extracted text back to a number for comparisons or arithmetic. Without VALUE, the result is a text string that won’t participate in numeric operations.
Example 4: LEFT for email local-part
Pull everything before the @ sign — the user portion of an email address.
Returns ava from ava@formulagym.com. Useful for mail-merge greetings, anonymising logs, or grouping rows by local part. The domain is the mirror job for MID or RIGHT.
Common LEFT errors and fixes
Four failure modes, each with what to check and how to recover.
LEFT returns #VALUE!
Cause: num_chars is negative, or the text argument is an error value like #N/A.
Guard the value with MAX(0, ...) if it’s computed. Wrap the whole expression in IFERROR for upstream errors.
LEFT number result won’t add
Cause: LEFT returns text, even when extracting digits from a numeric string. Arithmetic on text fails or coerces weirdly.
Wrap with VALUE(): =VALUE(LEFT(A2, 4)). Or multiply by 1 for a shorter idiom. Applies equally to RIGHT and MID.
LEFT with FIND returns #VALUE! on missing delimiters
Cause: the delimiter doesn’t exist in the source, so FIND returns #VALUE! and poisons the LEFT call.
Wrap in IFERROR with a safe fallback: =IFERROR(LEFT(A2, FIND("-", A2) - 1), A2) — returns the original text when no dash is found.
LEFT truncating too aggressively on CJK text
Cause: in legacy DBCS Excel builds, some characters are 2 bytes; LEFT counts characters while LEFTB counts bytes.
In modern Unicode Excel (365 / 2021+), LEFT is character- accurate. In legacy or certain regional SKUs, switch to LEFTB if you need byte-accurate counts for interop with legacy systems.
LEFT vs RIGHT, MID & FIND
Four text-extraction tools. Pick by which end you’re cutting from and whether the split point is fixed or dynamic.
| Function | Cuts from | Args | Typical use |
|---|---|---|---|
| LEFT | Start of string | text, [num_chars] | Prefix, country code, ISO year |
| RIGHT | End of string | text, [num_chars] | File extension, last N digits, area tail |
| MID | Arbitrary position | text, start_num, num_chars | Substring from the middle — month out of ISO date |
| FIND | (locator, not extractor) | find_text, within_text, [start] | Pair with LEFT/RIGHT/MID to extract around a delimiter |
Rule of thumb: LEFT cuts from the front, RIGHT cuts from the back, MID cuts from anywhere. FIND (case-sensitive) and SEARCH (case-insensitive) locate positions to feed into any of them. For repeated splits on the same delimiter, the modern TEXTSPLIT in Excel 365 often reads cleaner than nested LEFT / FIND chains.
LEFT frequently asked questions
6.01What does LEFT do when num_chars is bigger than the text?▸
It returns the entire string — LEFT silently clamps num_chars to the length of the text, no error. That makes LEFT safe to use with variable-length sources where padding is fine.
6.02How do I extract text up to a delimiter with LEFT?▸
Pair LEFT with FIND. =LEFT(A2, FIND("-", A2) - 1) returns everything before the first dash. The -1 drops the delimiter itself. If the delimiter might be missing, wrap with IFERROR to avoid #VALUE!.
6.03Does LEFT work on numbers?▸
Yes, but LEFT first coerces the number to text using the General format. LEFT(12345, 2) returns the text "12", not the number 12. Wrap with VALUE if you need a numeric result for arithmetic.
6.04Why does LEFT return the wrong characters on Japanese / Chinese text?▸
Double-byte characters count as one char in LEFT but two bytes in memory. LEFT works fine in Unicode Excel (365, 2021+), but in legacy builds with DBCS encoding you may need LEFTB to count bytes instead of characters.
6.05What’s the difference between LEFT and LEFTB?▸
LEFT counts characters; LEFTB counts bytes. On single-byte text they behave identically. On double-byte text (CJK, some emoji) LEFTB advances twice as slowly. In modern Unicode Excel, LEFT is almost always what you want.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.