Split Cell on Delimiter

What it does

Excel's Text to Columns wizard works for one delimiter at a time and walks you through 3 dialog screens for every run. The macro is a single-prompt version. You give it the delimiter, it splits each cell in your selection, writes each part to a column to the right. Each part is also trimmed (leading/trailing whitespace removed). Useful for comma-separated tags, semicolon-separated lists, pipe-delimited records.

Real-world example

Picture a marketing analyst at a B2B SaaS company importing campaign tags from the marketing automation platform. The platform exports tags as semicolon-separated strings: enterprise;decision-maker;cloud-native;trial-active. She needs each tag in its own column so she can pivot by tag.

Her current method: Data > Text to Columns > Delimited > Next > check "Other" > type semicolon > Next > Finish. About 30 seconds per column. With 4 tag columns to split, that's 2 minutes plus the inevitable misclick where she forgets to uncheck a previously-used delimiter.

With this macro she selects the tag column, runs the tool, types ;, and the cells split in 1 second. Popup confirms count. She runs it on the 3 other tag columns. Total elapsed: about 30 seconds.

On a 4-column campaign-tag split, expect about 90 seconds saved. The bigger win is consistent trimming: each split part comes back trimmed of whitespace, so enterprise; decision-maker (with stray space) doesn't produce decision-maker with leading space.

How to use

First time? See How to run our tools for the 30-second setup.

1. Make sure the columns to the right of your source column are empty.
2. Select the single column to split.
3. Press Alt + F8 (Windows) or Option + F8 (Mac), pick SplitCellOnDelimiter, click Run.
4. Type the delimiter. For tab, type the word Tab. The default suggestion is comma.
5. The macro splits each cell, writing parts to the columns to the right with whitespace trimmed.

FAQ

What delimiters does it support?

Any single-character delimiter (comma, semicolon, pipe, slash, period, dash, etc.). For tab, type the word Tab and the macro converts to a real tab character. Multi-character delimiters work too if you type the full string (e.g., | to split on space-pipe-space).

What about cells with quoted values containing the delimiter?

The macro doesn't handle CSV-style quote escaping. So "value, with comma", next value splits at every comma including the one inside quotes. For full CSV parsing, use Power Query's "From CSV" feature or a different tool.

Does it overwrite columns to the right?

Yes. The macro writes part 1 to column +1, part 2 to column +2, etc. If those columns have data, that data gets overwritten. Insert blank columns first.

Will it skip formulas?

Yes. Cells with formulas are skipped because splitting a formula doesn't make sense (the formula's result is what would split, not the formula itself). Convert formulas to values first with Convert Formulas to Values if you need to split formula results.

Can I undo it?

Yes, Ctrl/Cmd + Z right after running undoes the writes to the destination columns. Source cells aren't modified, so undo is clean. The macro runs entirely offline.

What to do next

If your destination ends up with extra trailing empty columns (because some rows have fewer parts than others), use Delete Empty Columns to clean up. For splitting names specifically (which has its own quirks), use the dedicated Split Full Names Into First and Last tool.