Data Cleaning & Text
Replace Inconsistent Delimiters with a Clean Standard Separator (SUBSTITUTE)
Normalize mixed semicolon, comma, and optional-space delimiters into a consistent pipe or CSV separator using nested SUBSTITUTE and TRIM without double spaces.
Collapse optional spaces around old delimiters before inserting the new one
CRM and form exports frequently mix "SKU-10; SKU-20;SKU-30" — some semicolons have a trailing space and others do not. Running a single SUBSTITUTE(B2, ";", " | ") leaves ugly double spaces ("SKU-10 | SKU-20 | SKU-30")!
First normalizing "; " to bare ";" via SUBSTITUTE(TRIM(B2), B3 & " ", B3) and then replacing B3 with B4 produces clean, uniform spacing across every token.
| Raw Input String | After Collapsing "; " -> ";" | Final Output (" | ") |
|---|---|---|
| SKU-10; SKU-20;SKU-30 | SKU-10;SKU-20;SKU-30 | SKU-10 | SKU-20 | SKU-30 |
| EU-West; US-East; APAC | EU-West;US-East;APAC | EU-West | US-East | APAC |
Normalized Delimiter Replacement Formula
Collapses spaces adjacent to old delimiter B3 before replacing every B3 with new delimiter B4.
=IF(OR(ISBLANK(B2), ISBLANK(B3)), B2, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(B2), " " & B3, B3), B3 & " ", B3), B3, B4))Delimited Item Count via Character-Length Difference
Counts items in normalized string B2 by comparing LEN before and after stripping B3.
=IF(LEN(TRIM(B2)) = 0, 0, LEN(B2) - LEN(SUBSTITUTE(B2, B3, "")) + 1)Try the example
Use the original values or change them to test your understanding.