Excel TEXTSPLIT with Multiple Delimiters: Keep Empty Fields in the Right Column

Check availability and prepare two source strings

This guide targets current Microsoft 365 and Excel 2024 editions that support TEXTSPLIT. Do not assume the function exists in Excel 2021 or earlier simply because those editions support other dynamic arrays. If Excel shows an unknown function name, verify the installed version before investigating separators or changing the data.

For the worksheet procedure, put R-07,North;High|Open in A2 and R-08,,Low|Closed in A3. Put record, region, priority, and status in C1:F1. The downloadable CSV has a case label and a quoted raw_text field for documentation, so copying its entire table into A1 would create a different layout. Copy the raw_text strings alone into the stated cells or use the setup text file.

Split on any of three alternative delimiters

Enter the formula once in C2 and copy C2 down to C3. Each formula reads one source cell and returns horizontally across the row. The array constant identifies comma, semicolon, and pipe as alternative field boundaries; these are not three sequential cleanup passes. Leave the destination cells C2:F3 clear before entering the formulas.

For row 2, the output should be R-07 in C2, North in D2, High in E2, and Open in F2. Check both the labels and the positions. This tiny fixture is intended to make a shifted field obvious, which is harder when a production record has dozens of similar-looking numeric fields.

=TEXTSPLIT(A2,{",",";","|"},,FALSE)

Preserve the missing region instead of shifting the record

Row 3 contains two consecutive commas after R-08. The segment between those commas represents a missing region, not an accidental extra punctuation mark under this record contract. With FALSE, D3 remains an empty field, Low stays in E3 under priority, and Closed stays in F3 under status.

Try the TRUE variation in a separate result area H3, with four empty cells available. That result has three fields: R-08, Low, and Closed. It is a reasonable compact list, but it is wrong if you label the second field region. Keep the two outputs separate while comparing them rather than overwriting the correct result with an experimental formula.

=TEXTSPLIT(A3,{",",";","|"},,TRUE)

Separate row delimiters from column delimiters

A different input can contain multiple records inside one cell. For a separate exercise, put R-11,North|R-12,South in A6 and enter the formula below in C6. Here comma separates fields across columns, while pipe separates records down rows. C6:D7 should contain two records, each with two fields.

Do not add pipe to the column delimiter list at the same time when it is meant to mark a new record. A separator has a structural role defined by the export format. Decide whether it means another field, another record, or literal content before choosing a formula. Otherwise a superficially successful split can flatten multiple records into one long row.

=TEXTSPLIT(A6,",","|",FALSE,0,"[missing]")

Distinguish missing padding from an explicitly empty field

Change A6 to R-11,North|R-12 for one further test. The second record now has fewer fields than the first. With the same formula, C7 contains R-12 and D7 contains the chosen marker [missing]. That marker makes an uneven record visible to the reviewer instead of silently pretending the export was complete.

A missing trailing field in an uneven row and an explicitly empty segment between two separators are related but different cases. Keep a validation column or a review note for each rejected record when adapting this to a recurring import. The split formula tells you where fields are; it does not prove that every required field was supplied or that each value has the intended meaning.

Use a CSV importer for quoted CSV records

The mixed-delimiter examples assume separators never occur as literal characters inside a field. A genuine CSV record can contain a quoted value such as "North, East", where the comma is part of the value. Splitting every comma without handling quoting would divide that one value incorrectly. Use an import path that understands CSV quoting for that case.

Also review what should happen to surrounding spaces and numeric-looking identifiers. This guide preserves the text tokens as written and does not coerce them into dates or numbers. Test identifiers with leading zeros separately before adding a conversion step. Keep the original string until the mapped output passes a field-count and value check.

The accompanying expected files cover both empty-field policies and the two-dimensional example. Independent checks reproduce these synthetic delimiter rules in Python; they do not run Excel or promise general CSV compatibility. Before using the formulas at scale, test the actual delimiter characters and argument separators in your installed Excel locale.