How to Split a Cell on a Delimiter in Excel

The "comma-separated values in one cell" problem

A column has values like red, blue, green or tag1;tag2;tag3 and you need each item in its own cell. Excel's Text to Columns is the classic answer, but it's a 3-screen wizard with too many options. Here are the alternatives.

TL;DR — Key takeaways

• Text to Columns is built-in but takes you through 3 dialog screens.
• The =TEXTSPLIT() function (Microsoft 365) does it as a formula.
• Power Query has a Split Column step for recurring jobs.
• A VBA macro is a one-prompt version: type the delimiter, splits go to the columns to the right with whitespace trimmed.

Method 1: Text to Columns

The classic Excel wizard.

1. Select the column.
2. Data > Text to Columns.
3. Choose Delimited > Next.
4. Pick the delimiter (or check Other and type one).
5. Next > Finish.

Works. Three screens of clicks per run.

Method 2: =TEXTSPLIT() (Microsoft 365)

Recent Excel versions have =TEXTSPLIT(A2, ","), which spills the parts horizontally into adjacent cells.

=TEXTSPLIT(A2, ",")

Drop in a helper column. Excel auto-spills.

The catch: Microsoft 365 only. Excel 2019 and earlier don't have it. Also produces formulas; paste-as-values to lock.

Method 3: Power Query Split Column

For recurring jobs:

1. Load data into Power Query.
2. Right-click column > Split Column > By Delimiter.
3. Pick the delimiter, the split position (each occurrence, leftmost, rightmost), and Close & Load.

Refreshable for weekly imports.

The catch: setup is more than the payoff for a one-off.

Method 4: The free VBA macro

Download Split Cell on Delimiter. Free .xlsm with one macro.

1. Make sure columns to the right are empty.
2. Select the column to split.
3. Alt + F8, pick the macro, click Run.
4. Type the delimiter (e.g., , or ;).
5. The macro splits each cell, writes parts to columns to the right, trims whitespace from each part.

In place. One prompt. Works on any Excel version.

A common scenario: tag columns

A CRM exports campaign tags as semicolon-separated strings: enterprise;decision-maker;cloud-native. You need each tag in its own column to pivot.

1. Select the tag column.
2. Run Split Cell on Delimiter.
3. Type ;.
4. Done. Each tag in its own column.

Versus Text to Columns: the macro takes 5 seconds versus 30, plus the convenience of trimming whitespace per part automatically.

Frequently asked questions

What delimiters are supported?

Any single character (comma, semicolon, pipe, slash, etc.). For tab, type the word Tab.

What about multi-character delimiters?

The macro accepts any string as the delimiter. So | (space-pipe-space) works as a multi-character delimiter.

What if my values have quoted strings with embedded delimiters?

The macro doesn't handle CSV quote escaping. So "value with, comma", next value splits at the inner comma too. For full CSV parsing, use Power Query's "From CSV" feature.

Will it overwrite existing data to the right?

Yes. The macro writes to columns directly to the right of your selection. Insert blank columns first if needed.

Can I undo it?

Yes, Ctrl/Cmd + Z right after running.

What to do next

For splitting names specifically (which has compound-surname and middle-name quirks), use the dedicated Split Full Names Into First and Last. To clean up empty trailing columns after a split, Delete Empty Columns.