How to Split Text into Columns in Excel

Method 1: use TEXTSPLIT for a live result

TEXTSPLIT, TEXTBEFORE, and TEXTAFTER need Microsoft 365 or Excel 2024. With region and code stored as “North, N-14” in A2, =TEXTSPLIT(A2,", ") splits at the comma-plus-space and spills the pieces across the row. Use the exact delimiter that appears in the data: splitting on "," alone would leave a leading space on every second piece. A row delimiter spills downward instead: =TEXTSPLIT(A2,,";").

Because TEXTSPLIT is dynamic, changes in A2 update the outputs. Keep the spill area empty; a non-empty cell in the way produces #SPILL!. In an older version, use LEFT, MID, RIGHT, and FIND for stable patterns, or Text to Columns for a one-time conversion.

Method 2: use Text to Columns

Text to Columns is best for a one-time split. Select only the source column, then choose Data > Text to Columns. Choose Delimited for commas, tabs, semicolons, or spaces, or Fixed width when fields occupy known character positions. Preview the breaks before finishing and set a Destination with enough empty columns.

The command writes values, not formulas. If the destination already holds data, Excel asks whether to replace it; choose Cancel and pick an empty destination, or work on a copy. The final wizard step also lets you format a column as Text, Date, or General. Use Text for codes with leading zeros.

Method 3: Flash Fill or extraction formulas

Flash Fill recognizes a demonstrated pattern. Type the first result beside the source and choose Data > Flash Fill (Ctrl+E). It is convenient for consistent data, but it produces fixed values and may infer the wrong pattern on exceptions, so review the first, last, blank, and unusual rows.

To pull one piece out of a single-delimiter string, =TEXTBEFORE(A2,", ") returns the part before it and =TEXTAFTER(A2,", ") the part after it. If the delimiter is missing, both return #N/A, which is a useful signal for incomplete rows. Older-compatible formulas can use =LEFT(A2,FIND(",",A2)-1) for the first piece and a MID expression for the rest.

Common mistakes

A space delimiter splits multiword names and addresses into too many pieces, and one export may use a semicolon where another uses a comma. Never finish the wizard without checking its preview and destination. If the source is a real CSV file, importing through Data > From Text/CSV or Power Query is safer than splitting it by hand because a proper importer respects quoted fields. Preserve codes as text when leading zeros matter.