Why Is My LEFT Formula Not Working in Excel?

Errors & fixes · Updated November 20, 2026

Most LEFT formulas fail because Excel is reading the entry as text, the formula syntax or argument separator is wrong, or the source cell contains unexpected data. Check that the formula starts with an equals sign, uses the right separator for your Excel settings, and refers to the cell you intend; then verify the cell format and calculation mode.

What the LEFT function does

LEFT returns characters from the beginning of a text string. Its syntax is =LEFT(text, [num_chars]): text is the cell or value to read, and the optional num_chars is how many characters to return. If you leave that second argument out, Excel returns one character. LEFT counts characters from the left edge; it does not search for a particular word, separator, or pattern.

For example, with the text INV-4827 in A2, =LEFT(A2,3) returns INV. With the text AB-104 in A2, =LEFT(A2,2) returns AB. The result is text, even if the characters returned look like a number. That distinction matters if you later use the result in calculations or need to preserve leading zeros.

LEFT is available in current desktop Excel, including Microsoft 365 for Windows. It is also available in Excel for Mac and Excel for the web, though some keyboard shortcuts and interface details differ. The function is not the same as Excel’s RIGHT or MID functions: RIGHT takes characters from the end, while MID starts at a position you specify.

Check the formula syntax and separators

Start with the simplest possible test. A formula needs an equals sign at the beginning, the function name LEFT, an opening parenthesis, a text argument, and a closing parenthesis. If you want more than one character, add a comma and the character count. Do not type the square brackets shown in the function syntax; they indicate that the argument is optional.

In the assumed layout where A2 contains ABCD, =LEFT(A2,2) returns AB. If Excel reports that there is a problem with the formula as you enter it, your regional settings may require a semicolon instead of a comma. Excel uses the list separator configured for the computer, so in those settings enter =LEFT(A2;2); with the same A2 value, it also returns AB. Use one separator style, not both. The formula examples in this article use commas unless noted otherwise.

Check for a missing parenthesis, extra punctuation, a misspelled function name, or an argument accidentally placed outside the parentheses. An entry such as LEFT(A2,2) without the initial equals sign is generally treated as ordinary text, not a formula. If you copied a formula from a document or website, retype its punctuation: typographic quotation marks or other substituted characters can make a formula invalid.

Is Excel showing the formula instead of its result?

If the cell displays =LEFT(A2,2) rather than AB, Excel may be treating the entry as text. Select the cell and look at Home > Number Format. If the format is Text, change it to General, then edit the cell and press Enter so Excel parses the entry again. Changing the format alone may not convert a formula that was already entered as text.

Also check whether the entry begins with an apostrophe, as in ‘=LEFT(A2,2). A leading apostrophe tells Excel to store the following characters as text. Remove it if it was unintended. If many formulas appear literally across the worksheet, you may have toggled Show Formulas. In Excel for Microsoft 365 on Windows, press Ctrl+` (the grave-accent key, usually near the upper-left of the keyboard) to toggle the display, or use Formulas > Show Formulas. On Mac, use the Show Formulas command on the Formulas tab if the keyboard shortcut differs on your keyboard.

Look for common errors in the result

Different symptoms point to different causes. A formula that returns an error is not the same as a formula displayed as text, so note exactly what Excel shows before changing anything.

  • #VALUE! commonly appears when the num_chars argument is negative. It must be zero or a positive number. If the count comes from another cell or calculation, inspect that value too.
  • #NAME? can mean Excel does not recognize a function name or text in the formula. Check spelling and whether text values in a formula are enclosed in straight quotation marks. In some locales, the function name itself may be localized.
  • A formula-entry warning often indicates a syntax problem, such as the wrong argument separator, a missing parenthesis, or an invalid reference.
  • A blank result may be correct if the source cell is empty or the requested character count is zero. Confirm that the referenced cell contains the expected value.
  • The wrong characters usually mean the reference, count, or source text is not what you assumed. Verify the selected cell and inspect spaces, prefixes, and other characters at the start of the value.

For instance, with A2 containing ABC, =LEFT(A2,-1) returns #VALUE! because the character count is negative. With the same A2 value, =LEFT(A2,0) returns an empty string. A count larger than the text length is not an error: if A2 contains ABC, =LEFT(A2,10) returns ABC.

Make sure the cell reference and source data are right

A formula can be valid yet point at the wrong cell. Click the formula cell and inspect the colored reference shown while editing, or select the referenced cell to see its contents. If you filled the formula down or across, a relative reference such as A2 may have shifted. When you need a reference to stay fixed while copying, use dollar signs: in the layout where A2 contains ABCD and B1 contains 2, =LEFT(A2,$B$1) returns AB and keeps B1 fixed when copied.

Also distinguish a cell’s displayed appearance from its underlying value. A number formatted with leading zeros may actually be stored as a number without those zeros. LEFT reads the underlying value as text; it does not automatically preserve a custom number format. For example, if A2 is the numeric value 7 formatted to display as 007, the layout with that value in A2 and a count of 2 in the formula =LEFT(A2,2) returns 7, not 00. If the leading zeros are meaningful identifiers, store the identifier as text or deliberately convert and pad it before extracting characters.

Dates can cause a similar surprise. Excel stores dates as serial numbers and displays them using date formats. LEFT works on the underlying value converted to text, not necessarily the date format you see on screen. If you need the visible date components, first create text in the format you want with TEXT, then extract from that text. The key troubleshooting question is: what is actually stored in the source cell, rather than what does its formatting make it look like?

Check spaces, punctuation, and extraction logic

LEFT counts every character, including spaces, hyphens, slashes, and punctuation. If a value appears to begin with a space, then =LEFT(A2,3) includes that space in its three-character result. This can make a result look blank or shorter than expected. TRIM removes leading and trailing ordinary spaces and reduces repeated internal spaces, but it does not remove every special whitespace character that may come from copied web data.

If you want all text before a delimiter, a fixed character count is fragile. In the layout where A2 contains AB-104, =LEFT(A2,FIND("-",A2)-1) returns AB: FIND locates the hyphen, and subtracting one excludes it. If the hyphen is missing, FIND returns an error, so this approach assumes every source value has that delimiter. Check that assumption before filling the formula down a column.

When the desired part is not at the beginning, LEFT is the wrong tool by itself. Use MID when you know the starting position, or RIGHT when you need characters from the end. The function choice should match the pattern in the data; adding a longer LEFT formula will not make it search from the middle.

Check calculation settings and formula dependencies

If the formula is visible but its result does not update after you change the source, Excel may be using manual calculation. On Windows, go to Formulas > Calculation Options > Automatic. Excel for Mac also has calculation options on the Formulas tab, though the layout can differ. In manual mode, press F9 to calculate open workbooks; on some Mac keyboards, you may need Fn+F9. Automatic calculation is the normal setting for most workbooks.

Calculation mode is more likely to explain a stale result than a formula that has never worked. Also check whether the source cell itself is a formula returning old or unexpected content. LEFT simply reads the current value supplied by its reference; it does not repair upstream formulas or update source data from outside the workbook.

A practical troubleshooting sequence

Work from the simplest checks toward the less common ones. This avoids changing several things at once and makes it easier to identify the actual cause.

  1. Click the formula cell and confirm the entry begins with an equals sign and spells LEFT correctly.
  2. Verify the source reference and character count. Test with a known text value and a small positive count.
  3. If Excel rejects the entry, try the list separator used by your regional settings: comma or semicolon.
  4. If the formula is displayed literally, check for Text format or a leading apostrophe; change to General and re-enter it.
  5. If the result is an error or looks wrong, inspect the source value, including spaces, punctuation, formatting, and whether it is numeric or text.
  6. If a valid result stays outdated after an edit, check Formulas > Calculation Options > Automatic.

For a quick controlled test, put a short known text value in an unused cell and enter a LEFT formula that takes two characters. If that works, the function itself is available and the problem is likely specific to the original data, reference, formatting, or count. If it does not, focus on the formula-entry separator, cell format, and whether Excel is showing formulas.

Frequently Asked Questions

Why does LEFT return only one character?
When you omit num_chars, LEFT returns one character by default. Supply a second argument with the number you need, and confirm that the argument refers to a number rather than an unexpected blank or text value.

Why does my LEFT formula show as text?
The cell may be formatted as Text, the formula may have a leading apostrophe, or Show Formulas may be enabled. Set the cell to General and re-enter the formula; if formulas throughout the sheet are visible, toggle Show Formulas off.

Can LEFT extract numbers from a cell?
Yes, it can return characters from a value that looks numeric, but the result is text. That may affect later arithmetic, and it may not retain number formatting such as displayed leading zeros.

Why does LEFT give #VALUE!?
A negative num_chars value is a common cause. Check both the number in the formula and any cell or calculation supplying that argument; zero is allowed, but a negative count is not.

How do I get everything before a hyphen?
Use a formula that finds the hyphen and asks LEFT for the characters before it. This requires the hyphen to be present in each source value; otherwise, FIND returns an error.

Does LEFT work with dates?
It can return characters from a date’s underlying stored value, but that may not match the date as displayed. Convert the date to the intended text format first if you need to extract part of its visible representation.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.