Text
Remove first N or last N characters from a cell
Strip fixed-width prefixes, suffixes, or both from product codes safely without #VALUE! errors on short strings.
Prevent #VALUE! when trimming short or blank cells
Classic RIGHT(A2, LEN(A2) - N) and LEFT(A2, LEN(A2) - N) throw #VALUE! whenever a cell has fewer than N characters because LEN(A2) - N becomes negative.
Clamping the remaining length with MAX(0, LEN(A2) - N) strips N characters cleanly and returns an empty string "" whenever the input is shorter than N.
| Code (A) | DropLeft (B) | DropRight (C) | Drop First 3 | Drop Last 3 | Drop Both |
|---|---|---|---|---|---|
| ID-90452-US | 3 | 3 | 90452-US | ID-90452 | 90452 |
| AB | 3 | 3 | "" | "" | "" |
Remove first N, last N, or both ends simultaneously
To strip both a prefix of length B2 and a suffix of length C2 in one pass, use MID starting at B2 + 1 with length MAX(0, LEN(A2) - B2 - C2).
=RIGHT(A2, MAX(0, LEN(A2) - B2))Remove last N characters from the right
LEFT keeps everything except the trailing C2 characters.
=LEFT(A2, MAX(0, LEN(A2) - C2))Inputs and interpretation
Require nonnegative integer removal counts and text input; preserve leading zeroes before importing numeric-looking IDs. The examples use ASCII codes. Emoji, combining marks and surrogate pairs can have different character-counting behavior across engines/Excel compatibility versions; do not assume these formulas remove whole visual graphemes.
Try the example
Use the original values or change them to test your understanding.