Splitting Text by Delimiter in Excel and SQL Queries

Data arrives with several values packed into one cell: "red;green;blue", "Smith, John", "2026-10-29 14:30:00", a path with slashes, a list of tags with commas. The job is to unpack them, either into columns (one cell per piece in the same row) or into rows (one row per piece). Excel and SQL both have tools for it, and both make it too easy to split in a way that quietly loses information. This article walks through them with the choices that matter: what to do with empty pieces, spaces, quoted delimiters and order. It pairs with concatenating text with a separator both ways in Excel and is part of moving between Excel and SQL.

Sample data

A table of products with a delimited tag list, in A2:B4 and in products(product_id, tags):

The tags use a semicolon, with inconsistent spaces. The aim: one row per product and tag, trimmed, with blanks dropped.

Columns or rows?

If each row has a fixed number of parts (day, month, year; first and last name), split into columns. If the number varies (tags, line items, recipients), split into rows. Splitting a variable list into columns produces a ragged table with a column per maximum length, tag1, tag2, up to tag12, almost all empty. It cannot be filtered or counted without more work. Rows are almost always the right shape for lists, and they are what a database wants.

Excel: TEXTSPLIT

=TEXTSPLIT(B2, ";")

For product 1 this spills "red", " green " and "blue" across three cells. The spaces remain, which is why you wrap in TRIM:

=TRIM(TEXTSPLIT(B2, ";"))

TRIM accepts an array, so it cleans every piece. To spill down instead of across, put the delimiter in the second position of the row argument: =TEXTSPLIT(B2, , ";"), with the column delimiter empty and the row delimiter set. To skip empty pieces from consecutive delimiters ("a;;b"), set ignore_empty to TRUE as the fourth argument. Multiple delimiters go in an array constant: TEXTSPLIT(B2, {";", ","}).

For a whole column, turn every list into rows with one formula:

=LET(
  p, Products[product_id],
  t, Products[tags],
  pairs, MAP(p, t, LAMBDA(id, s, IF(s="", "", HSTACK(id, TRIM(TEXTSPLIT(s, ";")))))),
  pairs)

This quickly becomes hard to read; it shows why a query or Power Query is better for bulk unpacking. In Power Query, select the column, Split Column, By Delimiter, choose the delimiter and "Split into Rows" in the advanced options, then trim; the steps are recorded and refresh (splitting and merging columns in Power Query).

Excel: Text to Columns and Flash Fill

Data, Text to Columns runs a wizard that splits the selected cells by a delimiter or fixed width and writes the results as values beside the original. It is a one-shot action: it does not update when the source changes, and it overwrites cells to the right, so insert blank columns first. It is the fastest route for a one-time clean-up and the wrong one for a repeating import. Flash Fill (Ctrl+E) infers a split from an example you type; it is convenient and inconsistent on irregular data, and also static.

In versions before TEXTSPLIT, split with MID, FIND and SUBSTITUTE, as in TEXTSPLIT, TEXTJOIN and text cleanup formulas.

SQL: the nth piece

SELECT product_id,
       trim(split_part(tags, ';', 1)) AS tag1,
       trim(split_part(tags, ';', 2)) AS tag2
FROM products;

split_part(string, delimiter, n) returns the nth piece, counting from 1. If there are fewer than n pieces, it returns an empty string, not NULL and not an error. Negative n counts from the end in recent PostgreSQL versions. It suits fixed-shape strings such as paths or codes (split_part(path, '/', 3)). It does not help when the number of pieces varies.

SQL: arrays and rows

For lists, split into an array and unnest it into rows:

SELECT p.product_id, trim(t.tag) AS tag, t.pos
FROM products p
CROSS JOIN LATERAL unnest(string_to_array(p.tags, ';')) WITH ORDINALITY AS t(tag, pos)
WHERE trim(t.tag) <> '';

string_to_array(tags, ';') produces an array, unnest expands it to rows and WITH ORDINALITY adds the position of each piece. The LATERAL join lets the function use the current row. The result for the sample:

Product 3 has a NULL or empty tags, so string_to_array returns NULL (or an array with one empty string) and the row disappears or is filtered out by the WHERE. If you need products with no tags to appear, use LEFT JOIN LATERAL ... ON true.

A regular expression splits on a pattern, such as a semicolon with surrounding spaces:

SELECT product_id, tag
FROM products, regexp_split_to_table(tags, '\s*;\s*') AS tag;

This removes the spaces as part of the delimiter, so no trim is needed, and it can split on several characters at once ('[;,]'). regexp_split_to_table is a set-returning function, usable in the FROM clause. string_to_array is faster for plain delimiters; use the regex form for messy ones.

Other engines have their own: SQL Server has STRING_SPLIT (2016 and later, with an ordinal argument from SQL Server 2022), MySQL has no built-in splitter and uses JSON_TABLE or SUBSTRING_INDEX tricks, and DuckDB has string_split and unnest.

Pitfalls

Empty pieces. "a;;b" splits into "a", "", "b". Decide whether the empty piece is a value. Filter it out unless it carries meaning (for example, a missing field in a fixed-position record).

Spaces and invisible characters. Trim every piece. A non-breaking space (character 160 in Excel, U+00A0) survives TRIM and trim(); replace it first (trimming spaces and non breaking spaces).

Delimiters inside values. A comma-separated list with quoted values ("Smith, John","Doe, Jane") cannot be split with a plain delimiter; the commas inside the quotes break it. That is a CSV parsing problem, and a CSV parser handles it (escaping quotes and newlines inside CSV). In SQL, parse such data with a CSV reader or regexp_matches with a pattern that respects quotes.

Case. ' Red' and 'red' are different tags until you normalize with lower(trim(...)).

Duplicates. A list can repeat a value ("red;red"). Use DISTINCT if each tag should appear once per product.

Position matters sometimes. In "first;second;third", order may carry meaning. WITH ORDINALITY in SQL and SEQUENCE in Excel preserve it.

Checking

Count the pieces: for the sample, the total number of tags is 4 (red, green, blue, blue). Compare with sum(cardinality(string_to_array(tags, ';'))) minus empties, or with LEN(tags)-LEN(SUBSTITUTE(tags,";",""))+1 per cell in Excel. Spot check the product with the most tags. Check that the number of distinct product IDs in the output equals the number with at least one tag.

When splitting is the wrong step

Splitting is a repair for data that was stored wrongly, and in a new design the right step is not to create the problem. A tags column in a table should be a child table product_tags(product_id, tag), or at most an array column with an index. If the source is a spreadsheet that people maintain, give them a template with one row per tag, or accept the packed list once and split it in the loader (designing tables from a messy Excel sheet). And if the split depends on natural language, such as splitting full names or addresses, a delimiter rule is not enough; see splitting full names and addresses in Excel. The PostgreSQL manual lists the string functions and array functions, and Microsoft documents TEXTSPLIT.