#VALUE! in LEFT/RIGHT/MID: Non-numeric length arguments

In Excel, the text extraction functions LEFT, RIGHT, and MID return a #VALUE! error when their numeric control arguments—specifically num_chars (the number of characters to extract) or start_num (the character position where extraction begins)—are non-numeric, negative, or resolve to an error. These arguments require valid positive integers. If a nested formula passes text, a space character, or a negative value into these arguments, the formula halts with #VALUE!.

Quick Fix

Ensure the length and start position arguments evaluate to zero or positive integers. If dynamic formulas like FIND() or SEARCH() drive the length calculation, wrap them in MAX(0, ...) or IFERROR(): =LEFT(A1, MAX(0, IFERROR(FIND("-", A1) - 1, 0))). This prevents negative numbers or lookup errors from passing into the text function.

---

Function Syntax and Parameter Constraints

To prevent #VALUE!, inputs for text extraction functions must meet specific operational boundaries:

• LEFT(text, [num_chars])
  • num_chars must be numeric and $\ge$ 0.
• RIGHT(text, [num_chars])
  • num_chars must be numeric and $\ge$ 0.
• MID(text, start_num, num_chars)
  • start_num must be numeric and $\ge$ 1.
  • num_chars must be numeric and $\ge$ 0.

If any parameter evaluates to a negative number, a text character (including spaces), or inherits an upstream #VALUE! from a nested formula, the parent text function will fail with #VALUE!.

---

Root Causes and Diagnostic Fixes

1. Negative Number Output from Math Offsets

The most common cause of #VALUE! in LEFT or MID occurs when calculating a delimiter offset that results in a negative number.

The Problem:
A formula is written to extract text preceding a dash:

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

If cell A2 begins with a hyphen (for example, "-TX-900"), FIND("-", A2) returns 1. The formula calculates 1 - 1 = 0, which works. However, if a cell contains "-" alone or text where the offset yields a negative value, or if an author writes FIND("-", A2) - 2, the argument becomes negative:

=LEFT("-TX", 1 - 2)  --> Evaluates to =LEFT("-TX", -1) --> Triggers #VALUE!

The Solution:
Constrain the result using MAX() so the extracted length can never fall below zero:

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

---

2. Upstream Error Inheritance from FIND or SEARCH

When nesting functions, an error inside the inner function immediately cascades outward.

The Problem:

=MID(A2, FIND(":", A2) + 1, 5)

If cell A2 does not contain a colon (":"), the inner FIND returns #VALUE! as explained in #VALUE! in FIND/SEARCH: When the string is not found. MID attempts to calculate #VALUE! + 1, which fails and reports #VALUE! at the cell level.

The Solution:
Trap the nested lookup with IFERROR before MID evaluates the start position:

=IFERROR(MID(A2, FIND(":", A2) + 1, 5), "")

Or use modern dynamic text formulas that remove the need for manual character counting:

=TEXTAFTER(A2, ":", , , , "")

---

3. Parameter Misplacement in MID

The MID function requires three parameters: text, start_num, and num_chars. A frequent mistake is transposing the second and third arguments or omitting start_num.

=MID(A2, "first", 5)    <-- Fails: "first" is text, not a starting index
=MID(A2, 0, 5)          <-- Fails: start_num cannot be less than 1

The Solution:
Verify your parameter order:

1. text: The source cell reference.
2. start_num: A whole number greater than or equal to 1.
3. num_chars: A whole number greater than or equal to 0.

If start_num is dynamically calculated, enforce the minimum bound of 1 using MAX:

=MID(A2, MAX(1, Calculation), Length)

---

4. Referencing Cells Containing Spaces or Text for Length Values

If your formula pulls its length argument from another cell (e.g., =LEFT(A2, B2)), verify what cell B2 contains:

• If B2 contains a space (" "), an empty text string (""), or letters, LEFT cannot interpret it as a number and throws #VALUE!.
• If B2 was populated by an import where numbers are stored as text with hidden carriage returns or non-printing characters, coercion may fail as described in #N/A because of Hidden Non-Printing Characters (CLEAN function fix).

Diagnostic Step:
In an empty cell, test the parameter source:

=ISNUMBER(B2)

If this returns FALSE, convert the value to a number directly inside the formula using VALUE() or mathematical coercion (+0):

=LEFT(A2, IFERROR(VALUE(TRIM(B2)), 0))

---

Step-by-Step Isolation Using the Formula Evaluator

When dealing with long, multi-layered text formulas like:

=MID(A2, SEARCH("[", A2) + 1, SEARCH("]", A2) - SEARCH("[", A2) - 1)

Tracing the specific argument that breaks can be difficult by sight alone:

1. Select the cell displaying #VALUE!.
2. Navigate to the Formulas tab on the Ribbon and click Evaluate Formula following Using the “Evaluate Formula” tool to trace the root of #VALUE!.
3. Click the Evaluate button repeatedly.
4. Watch the underlined expression. Check whether:
   • A search term resolves to #VALUE! because a bracket is missing.
   • The calculation for num_chars (SEARCH("]", A2) - SEARCH("[", A2) - 1) results in a negative number (which happens if the closing bracket appears before the opening bracket in the text string).

Once the specific failing expression is located, apply MAX(0, ...) for negative lengths or wrap the search term in IFERROR() to ensure valid numeric inputs.

For general troubleshooting of numeric and logical argument failures in Excel, see Fixing #VALUE!, #DIV/0!, and #NUM! Errors: The Excel Data Logic Guide.