LEFT function

LEFT returns the leftmost characters from a text string, optionally limited by a specified number of characters.

=LEFT(text, [num_chars])

Generate a LEFT formula

Describe what you need. The generator will reach for LEFT where LEFT is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How LEFT reads its arguments
textrequirednum_charsoptionalLEFT →
ArgumentRequiredDescription
textRequiredThe source value; can be a string, number, or cell reference. Non-text values are coerced to text, and errors are propagated.
num_charsOptionalOptional. The number of characters to return. Must be a non-negative integer; if omitted, defaults to 1. Negative or non-numeric values trigger a #VALUE! error.

Returns

A text string containing the requested leftmost characters.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Create three-letter ingredient codes

IngredientSupplierUnitQtyExpiry Date
TomatoFreshFarmkg252024-05-01
BasilGreenLeafbunch122024-04-15
Olive OilSunCoL82025-01-01
CheeseDairyBestkg52024-03-20
=LEFT(A2,3)

Result: Tom

The formula looks at cell A2, which holds "Tomato". By asking for the first three characters, LEFT returns "Tom", a concise code you could use in inventory tags.

2. Get supplier initials

IngredientSupplierUnitQtyExpiry Date
TomatoFreshFarmkg252024-05-01
BasilGreenLeafbunch122024-04-15
Olive OilSunCoL82025-01-01
CheeseDairyBestkg52024-03-20
=LEFT(B3,2)

Result: Gr

Cell B3 contains the supplier name "GreenLeaf" for the basil row. LEFT extracts the first two characters, giving "Gr" which can serve as a quick supplier identifier in reports.

3. Extract expiry year from date

IngredientSupplierUnitQtyExpiry Date
TomatoFreshFarmkg252024-05-01
BasilGreenLeafbunch122024-04-15
Olive OilSunCoL82025-01-01
CheeseDairyBestkg52024-03-20
=LEFT(TEXT(E5,"yyyy-mm-dd"),4)

Result: 2024

The expiry date for cheese sits in E5 as a true date value. TEXT formats it as a string "2024-03-20"; LEFT then pulls the first four characters, yielding the year "2024" for age-based analysis.

Common errors

Which LEFT error are you seeing?
LEFT returned an error#VALUE!
Replace the negative number with a positive integer or use ABS(num_chars) to force a non-negative value.
#VALUE!
Convert the text to a number with VALUE("3") or simply enter the numeric literal 3.
#VALUE!
Reduce the requested length to 32,767 or fewer, or use MID to extract a segment when you need more than the limit.
ErrorWhy it happensHow to fix it
#VALUE!num_chars is negative (e.g., =LEFT(A2,-3)). LEFT cannot return a negative count of characters.Replace the negative number with a positive integer or use ABS(num_chars) to force a non-negative value.
#VALUE!num_chars is text that cannot be coerced to a number (e.g., =LEFT(A2,"three")). LEFT expects a numeric count.Convert the text to a number with VALUE("3") or simply enter the numeric literal 3.
#VALUE!num_chars exceeds Excel's limit of 32,767 characters (e.g., =LEFT(A2,40000)). The function cannot return more characters than the internal maximum.Reduce the requested length to 32,767 or fewer, or use MID to extract a segment when you need more than the limit.

Tips and when to use something else

  • If you need characters from the end of a string, reach for RIGHT instead of LEFT.
  • When the delimiter is a space or a comma, TEXTBEFORE or TEXTAFTER can extract the portion you want without counting characters.
  • Combine LEFT with TRIM to remove leading spaces before extracting, ensuring consistent results on messy data.
  • Use LEN to dynamically calculate how many characters to keep, for example =LEFT(A2, LEN(A2)-2) to drop the last two characters.

Related functions

RIGHTRIGHT returns the specified number of characters from the end of a text string, defaulting to one character if omitted.MIDMID extracts a specified number of characters from a text string, starting at a given position, and returns the resulting substring.TEXTBEFORETEXTBEFORE returns the substring before a given delimiter, letting you pick which occurrence and supply your own value when the delimiter is absent.FINDFIND returns the numeric position of a substring within a text string, counting from a specified start point and respecting case sensitivity.LENLEN returns the number of characters in a text string, counting letters, numbers, spaces and punctuation, and returns 0 for an empty cell.

Frequently asked questions

Why does LEFT sometimes return a number instead of text?
LEFT always returns text, but if the source cell contains a number, Excel first coerces the number to a string. The displayed result may look numeric, yet its underlying type is text, which can affect downstream numeric calculations.
Can LEFT handle arrays or ranges in a single cell?
In modern Excel, LEFT is array-compatible, so =LEFT(A2:A5,2) will spill a column of the first two characters from each cell. In Google Sheets the behavior is similar, but older Excel versions will return only the result for the first cell.
What happens if I omit the num_chars argument?
When num_chars is omitted, LEFT defaults to 1, returning only the very first character of the supplied text. This is handy for quick checks like extracting the first letter of a code.
How do I extract characters from a date without converting it to text first?
Dates are stored as serial numbers, so LEFT cannot operate directly on them. Wrap the date in TEXT with a format string (e.g., TEXT(E5,"yyyy-mm-dd")) and then apply LEFT to the formatted string.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17