Text & Data Cleaning
Remove the First Character or Strip the Last Word from Text Strings Safely
Trim leading symbol prefixes ("#INV-408" -> "INV-408") and strip trailing titles/units ("Elena Petrova PhD" -> "Elena Petrova") without #VALUE! errors on single-character or single-word cells.
Guarding empty/short strings and locating the final space with Nth SUBSTITUTE
To strip a single leading character such as "#" or "'", RIGHT(A2, LEN(A2) - 1) fails with #VALUE! when A2 is empty (LEN(A2) - 1 = -1). Using MID(A2, 2, LEN(A2)) (with an ISBLANK guard) safely returns "" on empty or 1-character cells without negative-length errors!
To drop the trailing word from a multi-word string, TRIM collapses duplicate spaces first, spCnt = LEN(txt) - LEN(SUBSTITUTE(txt, " ", "")) counts spaces, and SUBSTITUTE(txt, " ", "|", spCnt) marks ONLY the final space so LEFT(txt, FIND("|", ...) - 1) slices everything before the last word—returning txt unchanged when spCnt = 0.
| Operation | Input String | Cleaned Output |
|---|---|---|
| Remove 1st char | "#ORD-904" | "ORD-904" |
| Remove 1st char (1-char input) | "#" | "" (No #VALUE!) |
| Remove last word | " Elena Petrova PhD " | "Elena Petrova" |
| Remove last word (1-word input) | "Sofia" | "Sofia" (Single-word safe) |
Error-Proof Strip First Character Formula
Uses MID starting at position 2 so 1-character and blank inputs return "" instead of #VALUE!.
=IF(ISBLANK(A2), "", MID(A2, 2, LEN(A2)))Remove Last Word Formula (Single-Word Safe)
Normalizes spaces with TRIM, locates the Nth (last) space via SUBSTITUTE's instance_num argument, and slices everything to its left.
=LET(txt, TRIM(B2), spCnt, LEN(txt) - LEN(SUBSTITUTE(txt, " ", "")), IF(spCnt = 0, txt, LEFT(txt, FIND("|", SUBSTITUTE(txt, " ", "|", spCnt)) - 1)))Try the example
Use the original values or change them to test your understanding.