Text & String Cleaning
Remove Text Between Variable Delimiter Positions (REPLACE & FIND)
Excise variable-length middle segments—such as bracketed revision tags "[REV-204B]" or internal cost codes—regardless of length by feeding FIND start and end positions into REPLACE(text, start, end - start + 1, "").
Surgically cut out variable-length middle tags when SUBSTITUTE cannot match fixed text!
SUBSTITUTE works only when you know the exact characters to delete—it cannot strip "[DRAFT-v1]", "[REV-204B]", and "[FINAL-APPROVED]" across 5,000 part descriptions in one pass because every bracketed tag has different text and a different character count!
Passing empty replacement text "" into REPLACE(old_text, start_num, num_chars, "") turns REPLACE into a precision scalpel! Locate the opening bracket at s = FIND("[", B2) and the closing bracket at e = FIND("]", B2, s), compute exact span num_chars = e - s + 1, and wrap inside TRIM(REPLACE(B2, s, e - s + 1, "")): "Pump Assembly [REV-204B] Stainless" cleanly becomes "Pump Assembly Stainless"—and wrapping in IFERROR(..., TRIM(B2)) leaves clean descriptions without brackets completely untouched!
| Raw Catalog Description (B2) | Start s = FIND("[") & End e = FIND("]") | Excised Span (e - s + 1) | Cleaned Output |
|---|---|---|---|
| "Pump Assembly [REV-204B] Stainless" | s = 15, e = 24 | 10 chars removed | "Pump Assembly Stainless" |
| "Valve Kit [QA-HOLD-99812] Brass" | s = 11, e = 25 | 15 chars removed | "Valve Kit Brass" |
| "Gasket Ring Viton" (No Brackets) | FIND Error -> IFERROR Fallback | 0 chars removed | "Gasket Ring Viton" |
Remove Variable-Length Bracketed Tag & Normalize Spaces
Excises "[" through "]" via REPLACE(..., "") and collapses the remaining double space via TRIM.
=IFERROR(TRIM(REPLACE(B2, FIND("[", B2), FIND("]", B2, FIND("[", B2)) - FIND("[", B2) + 1, "")), TRIM(B2))Count Exact Number of Excised Tag Characters (e - s + 1)
Returns the character length of the excised bracketed tag (or 0 when no bracketed tag exists).
=IFERROR(FIND("]", B2, FIND("[", B2)) - FIND("[", B2) + 1, 0)Try the example
Use the original values or change them to test your understanding.