How to Convert Text to Columns in Excel

If you need to split combined text in a single Excel column into separate columns, pick the tool that matches the task: use the built-in Text to Columns wizard for quick, one-off splits; use Power Query for repeatable or messy data; or use formulas when you need dynamic results. Work on a copy and preview results before you overwrite the original column.

Choose the right approach

Which method you choose depends on how often you will repeat the operation, the complexity of the source text, and whether you need the split to update automatically when the source changes.

One-off or simple splits (commas, tabs, spaces): Text to Columns wizard.
Repeatable, messy data or multi-step cleaning: Power Query. See Use Power Query to split and transform columns for a practical introduction.
Dynamic worksheets where output must update automatically: TEXTSPLIT and related formulas (availability varies by Excel build).

Method 1 — Text to Columns wizard (step-by-step)

Text to Columns is available from the Data tab and is the fastest way to split a column when the delimiter is consistent or fields are fixed width.

Select the column that contains the combined text. If you have adjacent data, insert empty columns to the right to avoid overwriting.
On the Data tab, click Text to Columns to open the wizard.
Choose Delimited when data uses characters such as commas, tabs, semicolons, or spaces; choose Fixed width when fields align in columns of the same width. Click Next.
For Delimited, select the delimiter(s) that match your text (for example, Comma). For Fixed width, click to set break lines where you want columns to split. Click Next.
On the final step, set each destination column type (General, Text, Date) and the destination cell where the split data will start. Click Finish and review results.

Delimited vs Fixed width — how to decide

Delimited is appropriate when a character separates each field (commas, semicolons, pipes, tabs, or spaces). Fixed width is appropriate when each field occupies the same number of characters, for example, exported reports with column-aligned values.

Delimited: flexible, handles variable-length fields, common for CSV or TSV files.
Fixed width: precise, used for legacy systems or aligned text exports.

Worked example: splitting "Smith, John" into two cells

Place "Smith, John" in A2. Select column A and run Text to Columns.
Choose Delimited, select Comma, click Next.
Set Destination to B2 if you want last name in B2 and first name in C2, then Finish.
If needed, trim spaces with TRIM or use Text to Columns again using Space as a delimiter.

Method 2 — Power Query for repeatable splits and complex data

Power Query (called Get & Transform in some Excel versions) imports and transforms data using a recorded series of steps you can refresh. This is the right choice when you regularly receive files with the same structure or when fields need additional cleaning.

Load your data: Data tab > From Table/Range. Confirm the table range and headers.
In the Query Editor, select the column and use Transform > Split Column. Choose By Delimiter or By Number of Characters depending on the pattern.
Customize the split: choose left-most, right-most, or each occurrence, and trim or change types as needed.
When finished, click Close & Load to return the cleaned, split table to Excel. Future file imports with the same structure can be refreshed to repeat the same steps.

Power Query can also handle nested splits, pattern-based splits, and replacements before splitting. For a guided walkthrough, see Use Power Query to split and transform columns.

Method 3 — Formulas and dynamic arrays

Formulas provide dynamic splitting that updates with the source. Modern Excel includes TEXTSPLIT and related functions; older versions require combinations of FIND, LEFT, MID, RIGHT, SEARCH, and SUBSTITUTE.

TEXTSPLIT: splits by one or more delimiters and returns a spill range. Useful for comma-separated lists and when you want automatic updates. Availability varies by Excel subscription and build.
TEXTBEFORE and TEXTAFTER: extract the portion before or after a specific delimiter for simpler cases.
Classic formulas: use FIND or SEARCH with LEFT/MID and SUBSTITUTE to handle repeated delimiters without dynamic array support.

Example with TEXTSPLIT: =TEXTSPLIT(A2, ",") spills each comma-separated item into its own column. If TEXTSPLIT is not available, the classic formula path is longer but possible.

After splitting: check data types and dates

Splitting text can produce values that remain as text even when they look like numbers or dates. Confirm column data types before performing calculations or sorting.

Numbers: convert text numbers to numeric values with Value or Text to Columns (set type to General) or multiply by 1.
Dates: if a field looks like a date but Excel stores it as text, use Date parsing functions or Convert delimited text into Excel dates and numbers for methods to coerce formats safely.
Leading zeros: set column type to Text if you need to preserve leading zeros (zip codes, product codes).

Common mistakes and troubleshooting

These are frequent issues and how to avoid them.

Overwriting adjacent data: always insert blank columns to the right of the source before splitting or set a safe Destination in the wizard.
Wrong delimiter selected: preview the split step in the Text to Columns wizard and test on a small sample first.
Dates misinterpreted: Excel may misread day and month fields depending on locale; verify and convert using explicit Date parsing if needed.
Inconsistent delimiters: when your data mixes commas and semicolons or includes quoted fields, use Power Query or formulas that handle quoted text.
Loss of leading zeros: choose Text data type or add a formatting step in Power Query to preserve them.

Quick checklist before you split

Make a copy of the sheet or file to avoid accidental loss of data.
Insert empty columns to the right of your data or choose a safe Destination cell.
Preview the split on a few rows to confirm delimiters or break positions.
Decide whether split results should be static (Text to Columns) or dynamic (formulas/Power Query).
Verify data types, especially numbers and dates, and apply conversions if necessary. For guidance on dates, see Convert delimited text into Excel dates and numbers.

When to use specialized workflows

Some scenarios need more than a simple split.

CSV files with quoted fields or embedded delimiters: import with Power Query and set the delimiter and quote character explicitly, or use a parser that understands CSV quoting.
Multiple delimiters and irregular patterns: use Power Query custom column formulas or text functions in Excel, or combine TEXTSPLIT with TRIM and SUBSTITUTE.
Large datasets updated frequently: automate with Power Query so you can refresh instead of repeating manual steps. For splitting comma lists into separate columns, see Split comma-separated values into columns in Excel.

Converting text to columns is a routine but important task. Choose the right tool for frequency and complexity, preview before committing, and verify types afterwards. With those steps, you can split text reliably and avoid common pitfalls.