Tutorials & Guides

Text to Columns: split data and fix numbers stored as text

When one column is holding two pieces of information — Last, First, City ST, INV-10428 — Text to Columns breaks it apart cleanly and predictably. It's the dependable sibling of Flash Fill: less magic, more control.

Splitting on a delimiter

1. Select the column (insert a couple of empty columns to its right first, so the split has room to land).
2. Data → Text to Columns.
3. Choose Delimited, click Next, and tick the delimiter — comma, space, tab, or a custom character.
4. Click Finish.

Doe, Jane becomes Doe and Jane in two columns. For a comma-and-space like Doe, Jane, tick both Comma and Space and enable "Treat consecutive delimiters as one."

Fixed width, for data with no delimiter

If the data lines up by position rather than a separator (common in exports from old systems), choose Fixed width instead and drag the break lines where you want the cuts.

The hidden superpower: fixing numbers stored as text

Numbers pasted from a PDF or a web page often arrive as text — they left-align, show a little green triangle, and refuse to sum or match. Fix a whole column in three clicks: select it, Data → Text to Columns → Finish (don't change anything). Excel re-parses each cell and converts the text to real numbers. This one trick resolves a huge share of "why won't my lookup match?" problems.

Where it trips up

Text to Columns overwrites the columns to the right without warning — always leave empty space or copy the data first. And it's a one-time action, not live; if the source changes, run it again (or use TEXTSPLIT on modern Excel for a formula that recalculates).

Numbers-as-text is the number-one cause of broken matches. Before any comparison, also run TRIM and CLEAN — and see the full method in how to compare two lists in Excel.