How to Use Text to Columns in Excel

Text to Columns splits the contents of one column into several, using a delimiter such as a comma, or fixed character positions. It is also the quickest fix for a column of numbers that Excel is treating as text, and for dates that imported in the wrong order. For analysts cleaning exports and pasted data, it is a fast, no formula way to make raw data usable.

What Text to Columns does and when to use it

Text to Columns takes a single column and parses each cell into multiple columns. Delimited mode splits on a character you pick, such as a comma, tab, semicolon, or space. Fixed width mode splits at column positions you draw, which suits aligned reports with no separators.

Reach for it when an export lands everything in one column, when a full name needs splitting into first and last, or when pasted numbers and dates come in as text. It is a one time transform on a selection, so it fits ad hoc cleanup better than a repeatable pipeline, where Power Query would be the better choice.

Step by step

To split Last, First in column A into two columns:

1. Insert a blank column to the right so the split has room to land without overwriting data.
2. Select the column to split, then go to Data > Text to Columns.
3. Choose Delimited and click Next.
4. Check Comma (and Space if needed), watching the Data preview, then click Next.
5. For each output column, set the Column data format, then click Finish.
6. Confirm the overwrite warning if Excel asks.

Always leave empty columns to the right so the split does not overwrite neighboring data.

A model use case

A common cleanup is a downloaded column of amounts that arrives as text, often shown left aligned with a green triangle, so SUM ignores it. Select the column, run Data > Text to Columns, click Finish on step one with format set to General, and Excel reparses each cell as a real number in place. No delimiter is needed; the act of finishing the wizard does the conversion.

The same trick fixes dates that imported in the wrong order. On the final step, set the column data format to Date and choose the source order, for example MDY or DMY, so a string like 03/04/2026 lands as the date you actually meant rather than text or a flipped day and month.

• Finishing the wizard with General format converts text numbers to real numbers.
• Set the Date format and source order to fix flipped or text dates.
• Use the Text format on a column to preserve leading zeros, such as account codes.

Pitfalls and limits

Text to Columns overwrites the columns immediately to the right without much warning, so always clear or insert space first. It is also a one shot operation: it does not refresh when source data changes, so for a recurring import use Data > Get Data and Power Query, which records the steps and can rerun them.

Watch the format on each output column. Leaving a code like 00123 or a long number on General will strip leading zeros or convert it to scientific notation, so set those columns to Text in the final step. Finally, Text to Columns reads the delimiter you choose; data containing that same character inside a quoted field can split incorrectly, in which case importing through Power Query, which respects text qualifiers, is safer.