Split a Spreadsheet Column Without Misaligning the Exceptions

A clean split starts by defining the pattern, not by clicking a command. If a column contains City | Region | Country, decide that every valid row should produce exactly three fields and preserve empty fields such as London | | GB.

record_id	compound_location	city	region	country	split_status
001	Portland | OR | US	Portland	OR	US	OK
002	London | | GB	London	(blank)	GB	OK—region missing
003	Washington, D.C. | DC | US	Washington, D.C.	DC	US	OK
004	Paris – Île-de-France – FR				Review—different separator

This structure keeps a missing middle value from shifting GB into the region column. It also makes exceptions visible before the file becomes a public table.

1. Define the split contract

Write down four things:

• the exact delimiter, such as |;
• the expected number of output fields;
• whether empty fields are valid;
• the output column names and meanings.

Choose a delimiter that does not also occur inside a field. Splitting a person’s name on spaces, for example, is unreliable because names do not have a fixed number of parts. A comma is also a poor separator when the values themselves can contain commas.

For a three-field value separated by a single |, each normal row should contain two delimiters. Put this helper formula beside the source data and fill it down:

=LEN(A2)-LEN(SUBSTITUTE(A2,"|",""))

Filter that helper column for anything other than 2. Inspect those rows before splitting; they may contain a missing delimiter, an extra field or a different character that only looks similar.

2. Preserve the source column

Keep the compound value as compound_location_raw, and place the split fields in new columns. Also retain a stable record ID so you can trace a bad result back to its original row.

This is especially important for publication: correcting a parsing rule is much easier when the raw input remains available. Do not include the raw field in the public export if it contains information readers should not see, and never publish sensitive data.

3. Split in Google Sheets

For a one-time operation, select the source cells and choose Data > Split text to columns, then select the separator. Google documents this workflow for clearly defined, delimiter-separated data and lets you change the separator from a dropdown (Google Docs Editors Help). Put empty destination columns to the right first, and work on a copy.

For an auditable formula-based split, enter this in the first output cell:

=SPLIT(A2,"|",FALSE,FALSE)

Google’s SPLIT function places fragments in separate cells across the row. Its fourth argument controls whether empty fragments are removed; setting it to FALSE retains an empty middle field (Google SPLIT documentation). The third FALSE tells Sheets to treat the delimiter as one complete string rather than splitting on each character—useful when the delimiter has multiple characters.

4. Split in Excel

For a one-time split, select the source column and choose Data > Text to Columns. Select Delimited, choose the delimiter, review the preview, and set a destination for the output (Microsoft Support).

For a formula-based workflow in a version of Excel that supports TEXTSPLIT, use:

=TEXTSPLIT(A2,"|",,FALSE)

Microsoft documents TEXTSPLIT as the formula equivalent of Text to Columns. Its ignore_empty argument defaults to FALSE, which creates an empty cell for consecutive delimiters (Microsoft TEXTSPLIT documentation). Writing the argument explicitly makes the intended treatment of missing fields clear.

5. Normalize and validate the result

After splitting:

1. Trim ordinary extra spaces. In Google Sheets, select the output and use Data > Data cleanup > Trim whitespace. Google’s documentation notes that this command does not trim non-breaking spaces, so inspect values copied from web pages if apparently identical categories still sort or group separately (Google Docs Editors Help).
2. Filter each output column for blanks. Decide whether each blank is valid, missing or caused by a malformed source row.
3. Review delimiter-count exceptions. Do not force them through the normal rule; correct the input or assign an explicit review status.
4. Check field meaning, not only field count. A three-part result can still be wrong if the source used a delimiter inside one value.
5. Protect code-like fields. Postal codes, account codes and other identifiers may need text formatting so leading zeros survive export. Use the CSV leading-zero checklist when the result will move between systems.
6. Document the new columns. Record names, definitions, datatypes and permitted values in a spreadsheet data dictionary.

Before publishing, export only the record ID, clean fields and any useful review/status field. Sort and filter the finished dataset once more: rows with blank required fields, unexpected country codes or leftover delimiter characters are the ones most likely to confuse readers of the public table.