Your LEFT, RIGHT, or MID formulas aren’t returning the expected text, results are blank, incorrect, or throw errors like #VALUE!.
This usually happens due to wrong positions, text formatting issues, or hidden characters.
Why the Issue Happens
- Incorrect character positions (start or length)
- Text vs number mismatch
- Extra spaces or hidden characters
- Using fixed positions on variable-length data
- Negative or zero length values
- Referencing wrong cells
- Data not actually in text format
Step-by-Step Fixes
Step 1: Use Correct Syntax
LEFT:
=LEFT(A2, 4)
RIGHT:
=RIGHT(A2, 3)
MID:
=MID(A2, 2, 5)
- MID → start position + number of characters
- Positions are 1-based (not 0)
Step 2: Fix Incorrect Positions
Example:
=MID(A2, 0, 3) ❌
Correct:
=MID(A2, 1, 3)
Position must start from 1 or greater.
Step 3: Handle Variable Length Text
If text length changes, fixed numbers fail.
Fix:
=RIGHT(A2, LEN(A2)-4)
Use LEN to dynamically extract text.
Step 4: Remove Extra Spaces
Hidden spaces shift positions.
Fix:
=LEFT(TRIM(A2), 4)
Step 5: Remove Hidden Characters
Imported data often contains invisible characters.
Fix:
=MID(TRIM(CLEAN(A2)), 2, 5)
Step 6: Fix Text vs Number Issues
If value is numeric:
=LEFT(A2, 2) ❌ may fail
Fix:
=LEFT(TEXT(A2, "0"), 2)
Step 7: Extract Based on Delimiters (Better Method)
Instead of fixed positions, use SEARCH.
Example:
=LEFT(A2, SEARCH("-", A2)-1)
Extracts text before -.
Step 8: Extract After a Character
=RIGHT(A2, LEN(A2) - SEARCH("-", A2))
Step 9: Avoid #VALUE! Errors
Occurs when:
- Start position exceeds length
- Length is negative
Fix:
=IFERROR(MID(A2, 2, 5), "")
Step 10: Debug Step-by-Step
Check:
=LEN(A2)
If length is unexpected → data issue.
Common Mistakes
- Using position 0 instead of 1
- Hardcoding positions for variable text
- Ignoring spaces and hidden characters
- Using text functions on numbers
- Not validating string length
- Incorrect cell references
Pro Tips
Extract before delimiter
=LEFT(A2, SEARCH(",", A2)-1)
Extract after delimiter
=MID(A2, SEARCH(",", A2)+1, LEN(A2))
Extract last word
=RIGHT(A2, LEN(A2) - FIND(" ", A2))
Bottom Line
If LEFT, RIGHT, or MID isn’t working, fix in this order:
- Check positions (start and length)
- Use LEN for dynamic extraction
- Remove spaces and hidden characters
- Convert numbers to text if needed
- Use SEARCH instead of fixed positions
Most issues come from wrong indexing or dirty data.
Fix those, and text extraction will work correctly.