How to Separate Data in a Cell in Excel (names, addresses, codes)

“Ana, Pune, 411001” in one cell needs to become three columns. Pick the method by how the pieces are separated.

Which method

Text to Columns

1. Select the cells. Check the columns to the right are empty.
2. Data › Text to Columns › Delimited › Next.
3. Tick Comma (and Space if there is a space after each comma — tick Treat consecutive delimiters as one). Next.
4. Click the PIN column in the preview › set it to Text so zeros survive. Finish.

Flash Fill

1. In B1 type Ana. In B2 start typing Raj — Excel greys in the rest. Press Enter. Or click B2 and press Ctrl + E.
2. Repeat in column C for the city.

TEXTSPLIT (Excel 365)

=TEXTSPLIT(A1, ", ")

Spills into as many columns as there are pieces. Older Excel: split a cell has the LEFT/MID formulas.

Check it worked

Scroll to the longest entry. Every piece landed in its own column; nothing was cut off.

Common mistake

A comma inside a piece (“Roy, Ana”) creates an extra column. Look at the preview before Finish.