The first thing most people do when they need to split 'Apple, Banana, Cherry' into three rows is paste it into a single cell and hit Data → Text to Columns. That only splits across columns — not rows. You’ll end up with A1='Apple', B1='Banana', C1='Cherry'. Wrong direction. Total waste of time.
Quick Answer
Use Power Query (Get & Transform) to split delimited text into rows in under 30 seconds — no formulas, no VBA, no manual copy-paste. Select your column → Data tab → From Table/Range → Transform tab → Split Column → By Delimiter → choose 'Rows' (not 'Columns'). Done.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Power Query (Split Column → Rows) | Select column → Data → From Table/Range → Transform → Split Column → By Delimiter → check 'Advanced options' → select 'Rows' | Comma/semicolon/line-break lists in large datasets (10k+ rows) | Requires Excel 2016+; won’t auto-update if source range changes without refresh |
| TEXTSPLIT + TRANSPOSE (Excel 365) | =TRANSPOSE(TEXTSPLIT(A2,";")) — enter as dynamic array formula in one cell | Single-cell values with consistent delimiters; no Power Query access | Only works in Excel 365/2021; spills vertically — can’t force exact row count |
| Flash Fill (Ctrl+E) | Type first item in adjacent cell → select next blank cells → Ctrl+E | Small batches (≤50 rows); predictable patterns like 'First Last' or 'City, State' | Fails on inconsistent spacing, mixed delimiters, or line breaks (Alt+Enter) |
| VBA macro (Split + Loop) | Paste code into Module → run on selected range → outputs to new sheet | Legacy Excel versions; repeatable automation for identical workflows | Security warnings; macros disabled by default; requires editing for each delimiter |
| SUBSTITUTE + FILTERXML (Windows only) | =FILTERXML(" |
Excel 2013+ Windows users needing formula-only solution | Fails if text contains '<', '>', '&'; crashes on empty cells or >100 items |
Method 1 Deep Dive
Let’s use real data from Alibaba supplier reports. In column A (A2:A6), you have:
| A2 | A3 | A4 | A5 | A6 |
|---|---|---|---|---|
| Acme Corp; $45,200; 2024-03-15 | Beta Ltd; $18,900; 2024-04-02 | Cortex Inc; $62,150; 2024-02-28 | DynaTech; $33,400; 2024-05-11 | Elite Group; $89,700; 2024-01-19 |
You want each semicolon-separated chunk in its own row — not column. Do this:
- Select A1:A6 (include header if present)
- Go to Data tab → click From Table/Range. Check 'My table has headers' if A1 says 'Supplier Info'.
- In Power Query Editor, right-click column 'Supplier Info' → Split Column → By Delimiter.
- In dialog: choose Semicolon, under Advanced options, select Rows (not Columns). Click OK.
- Click Close & Load in top-left. Output lands in a new worksheet as 15 clean rows.
Surprising tip: If your source data has line breaks (Alt+Enter inside a cell), use Custom delimiter → type CHAR(10) in box. Power Query reads it as a true line break — no regex needed.
Method 2 Deep Dive
For Excel 365 users who need speed and don’t want Power Query open, use TEXTSPLIT + TRANSPOSE. Try this on cell D2 containing:
Shenzhen Electronics; Guangzhou Logistics; Ningbo Manufacturing; Xiamen Trade
Do this in E2:
=TRANSPOSE(TEXTSPLIT(D2,"; "))
Note the space after semicolon — critical if your data uses '; ' not ';'. Press Enter. It spills down E2:E5 automatically.
But here’s what most miss: TEXTSPLIT ignores empty elements by default. If your source has Item1;;Item3, it skips the blank. To preserve it, add third argument: TEXTSPLIT(D2,";",,TRUE). That final TRUE = 'ignore empty' = FALSE.
Keyboard shortcut: To quickly jump into Power Query Editor after loading, press Alt+A+T. Then Alt+H+S+R opens Split Column → By Delimiter.
Cheat Sheet
| Task | Shortcut / Formula | Notes |
|---|---|---|
| Open Power Query Editor | Alt+A+T | Works only after data is loaded as query |
| Split column into rows (PQ) | Alt+H+S+R → choose delimiter → 'Rows' | No mouse needed — full keyboard workflow |
| Flash Fill (fast pattern extraction) | Ctrl+E | Type first result manually, select target range first |
| Force TEXTSPLIT to keep blanks | =TEXTSPLIT(A1,",",,FALSE) |
Fourth argument must be FALSE — TRUE skips blanks |
| Split on line breaks (Alt+Enter) | In PQ: Custom delimiter → #(lf) or CHAR(10) |
#(lf) is Power Query’s native line feed symbol |
