Excel LEFT Function

LEFT extracts a specific number of characters from the beginning of a text string and returns them as a new string. If you've ever needed the first three digits of a product code or the first name buried inside "Smith, John," this is the function. One thing to know up front: LEFT always returns text, even when the characters it pulls look like numbers. That trips up a lot of beginners the first time they try to add two "LEFT numbers" together and get an error.

Available in: Excel 365, Excel 2021, Excel 2019, Excel 2016, and earlier desktop versions, plus Excel Online and Excel for Mac. LEFT has worked the same way since it was introduced and hasn't changed across versions.

Syntax

=LEFT(text, [num_chars])

Parameter	Required	Description
text	Yes	The string (or cell reference) you're pulling characters from. Can be typed text, a cell, or a formula that returns text.
num_chars	No	How many characters to grab, counting from the left edge. Defaults to 1 if you leave it out.

Basic Example

Say you're managing a product list where every SKU starts with a three-letter category code, like FRN-88213 for furniture or ELC-40021 for electronics. You want that code pulled into its own column.

=LEFT(A2, 3)

// A2 = the full SKU, "FRN-88213"
// 3  = number of characters to pull from the start

This returns FRN. Excel starts at the leftmost character in A2 and grabs exactly three of them, ignoring everything after. Copy the formula down and every row returns its own category prefix, no matter what the rest of the SKU looks like.

How LEFT Works

It always counts from the start of the string

LEFT has no concept of "the middle" or "the end." It starts at character one and counts forward. If you need characters from anywhere else in a string, you want MID or RIGHT instead.

num_chars defaults to 1 if omitted

=LEFT(A2) with no second argument returns just the first character of A2. This is useful for quick checks, like testing whether a name starts with a specific letter, but it's easy to forget the argument entirely and get a truncated result you didn't expect.

num_chars larger than the string just returns the whole string

If A2 contains "Cat" and you write =LEFT(A2, 50), Excel doesn't throw an error. It returns "Cat" in full. LEFT never pads or errors when you ask for more characters than exist; it simply stops at the end of the string.

The result is always text

Even if the extracted characters are digits, LEFT hands them back as a text string, not a number. =LEFT("48213-Order", 5) returns the text "48213," which looks like a number but won't sum, average, or sort numerically until you convert it. More on the fix below.

Common Use Cases

Extracting a category prefix from product codes

You're cleaning a spreadsheet of thousands of SKUs and need the category segment isolated for a pivot table.

=LEFT(products[SKU], 3)   // returns the first 3 characters as the category code

Splitting "Last, First" names to get the surname

Your employee list has full names formatted as "Nguyen, An" and you need just the last name for a mail merge.

=LEFT(A2, FIND(",", A2) - 1)

// A2              = the full name, "Nguyen, An"
// FIND(",", A2)    = the position of the comma (7)
// -1               = drops the comma itself from the result

FIND locates the comma, and LEFT stops one character before it. This works even when names have different lengths, because the formula doesn't rely on a fixed character count.

Building a "begins with" test

You want to flag any order ID that starts with "RTN" as a return.

=IF(LEFT(A2, 3) = "RTN", "Return", "Standard Order")

LEFT pulls the first three characters of A2 and compares them against "RTN." Note that this comparison isn't case-sensitive: "rtn," "RTN," and "Rtn" all match.

Converting extracted digits into a real number

You pulled the first four digits of a code with LEFT but need to run math on them, like summing invoice prefixes.

=VALUE(LEFT(A2, 4))

// LEFT(A2, 4) = extracts "2026" as text
// VALUE(...)  = converts that text into a real number Excel can calculate with

Skip the VALUE wrapper and SUM, AVERAGE, and comparison formulas will either error out or silently treat the result as zero.

Handling Errors

LEFT is one of the more forgiving text functions, but it can still throw its own #VALUE! in a couple of specific situations. It can also show a completely different error, if that error is inherited from an upstream cell rather than caused by LEFT itself.

Common causes of a LEFT-specific #VALUE!:

• num_chars is a negative number (Excel can't extract "negative three" characters)
• num_chars is text instead of a number, like a stray letter typed into the second argument

If the text argument references a cell that already holds an error, LEFT doesn't convert it to #VALUE!. It passes the original error straight through unchanged: a #REF!, #N/A, or #DIV/0! upstream shows up as that same error in the LEFT formula, not as #VALUE!.

That distinction matters for how you handle it. IFERROR still catches any of these, since it doesn't care which specific error it traps. But one fallback message can't accurately describe every case: "must be a positive number" is right for a bad num_chars, and wrong for an inherited #REF!. If you want the fallback to actually help whoever sees it, either check num_chars separately from the error trap, or write a fallback message general enough to cover both causes.

=IFERROR(LEFT(A2, 3), "Check A2 for an upstream error, or check num_chars")

Notes & Gotchas

What is the LEFT function in Excel and what does it do?

LEFT extracts a specified number of characters starting from the left edge of a text string and returns them as text. It's one of Excel's three core substring functions, alongside RIGHT and MID, and it's typically used to isolate codes, prefixes, or the first part of a name from a longer string.

What is the syntax of the LEFT function?

The syntax is =LEFT(text, [num_chars]), where text is the string you're extracting from and num_chars is how many characters to pull. num_chars is optional and defaults to 1 if you omit it, meaning =LEFT(A2) alone returns only the first character of A2.

How do you extract a specific number of characters from the left side of a text string?

Supply the exact count as the second argument: =LEFT(A2, 5) pulls the first five characters of A2, no matter what they are. If you need a variable count instead of a fixed one, pair LEFT with FIND or SEARCH to locate a delimiter and calculate the count dynamically.

How can LEFT combine with other functions for text extraction tasks?

LEFT is rarely used alone in real workbooks. It's typically nested inside IF for conditional tests, VALUE to convert extracted digits into numbers, FIND or SEARCH to locate a dynamic cutoff point, and occasionally DATE when rebuilding a date from a text string like "20260713." Each combination solves a different extraction problem, but the underlying LEFT call stays simple.

How does LEFT handle numbers and formatting characters like commas or dollar signs?

LEFT treats formatted numbers as their displayed text, including commas, dollar signs, and decimal points, if the source is stored as text. But if the source cell holds an actual number formatted to display a dollar sign (like $1,240.00), LEFT reads the underlying value's text representation, not the formatting, and results can look inconsistent. For predictable results, apply LEFT to genuine text strings rather than formatted numeric cells.

Why does LEFT always return text, even from numbers?

LEFT is a text function by design, so its output is always a string, regardless of what the extracted characters look like. This means =LEFT(A2, 4) on a cell containing 20261307 returns the text "2026," which won't behave like a number in SUM, AVERAGE, or numeric comparisons. Wrap the result in VALUE() whenever you need to calculate with it.

Is LEFT case-sensitive?

No. LEFT extracts characters exactly as they appear but doesn't distinguish case when the result is compared elsewhere, such as in an IF test. =LEFT("apple", 3) = "APP" evaluates to TRUE, because Excel's text comparisons ignore case by default.

Does LEFT work with dates?

Not reliably. Excel stores dates internally as serial numbers, not text, so LEFT applied directly to a date cell extracts characters from the serial number, not the visible date. If you need the year or month from a date, use YEAR, MONTH, or TEXT to convert the date to a string first, then apply LEFT to that result.

What happens with emoji or double-byte characters like Chinese or Japanese text?

LEFT counts characters, not bytes, but certain Unicode characters, including emoji and some CJK (Chinese, Japanese, Korean) symbols, are stored as surrogate pairs internally. In rare cases this can cause LEFT to split a single visible character in half, returning a garbled or unreadable result. If your data includes emoji or non-Latin scripts, test the formula on a sample first rather than assuming a clean character count.

Why does my LEFT formula show a comma error or "too many arguments"?

This usually isn't a LEFT problem, it's a regional settings issue. Some locales use a comma as the decimal separator, which means Excel expects a semicolon between formula arguments instead of a comma. If =LEFT(A2, 3) throws a syntax error, try =LEFT(A2; 3).

Can I use LEFT in Excel's Data Validation rules?

Yes, but with limits. A simple formula like =LEFT(A2, 3) = "RTN" works fine as a custom Data Validation rule. Where it breaks down is when LEFT is combined with array constants, full-column references, or functions like LAMBDA; Data Validation explicitly rejects those, throwing an error about reference operators or array constants not being allowed.

What happens with leading or trailing spaces?

LEFT doesn't trim anything. If a cell has a leading space, like " Nguyen," =LEFT(A2, 3) pulls the space plus the first two letters, not the three letters you expected. This is especially common with data copied from the web, where non-breaking spaces sneak in invisibly. Wrap the source in TRIM() or CLEAN() before applying LEFT if you suspect stray whitespace.

Related Functions

Function	Use this when...
MID	You need characters from the middle of a string, starting at a specific position.
FIND	You need to locate a delimiter's position to make LEFT's character count dynamic.
TRIM	You need to strip extra spaces from a string before extracting characters with LEFT.