Text to Columns in Excel: Split One Column Into Many

Text to Columns in Excel splits one column of text into several columns, using a separator such as a comma or space, or a fixed number of characters. It is the quickest way to break up full names, product codes or data pasted from a CSV file. This guide walks through the wizard step by step and shows when a formula is the better choice.

What does Text to Columns do?#

Text to Columns takes each cell in a column and cuts it into pieces. Each piece goes into its own column to the right. For example, Sharma, Priya becomes Sharma in one column and Priya in the next.

It works in every desktop version of Excel, on Windows and Mac. The result is plain values, not formulas, so it's best for one-off clean-up of data that won't change.

How to use Text to Columns step by step#

Here is an export where each row holds an ID, a name and a department, separated by commas:

Step 1: Select the column and open the wizard#

Select A2:A4. Go to Data > Text to Columns (in the Data Tools group). On Windows you can press Alt, then A, then E. On a Mac, use the same button on the Data tab.

Step 2: Choose Delimited or Fixed width (wizard step 1)#

• Delimited means a character separates the parts, such as a comma, tab, space or semicolon. Choose this here.
• Fixed width means each part has the same number of characters, like a code where the first 3 characters are the region.

Click Next.

Step 3: Pick the delimiter (wizard step 2)#

Tick Comma and untick the others. The preview at the bottom shows where Excel will split. Useful options on this screen:

• Other lets you type any character, such as - or |.
• Treat consecutive delimiters as one stops double spaces from creating empty columns.
• Text qualifier (usually ") keeps a comma inside quotes, like "Lee, Marcus", from splitting.

Click Next.

Step 4: Set formats and the destination (wizard step 3)#

Click each column in the preview to set its Column data format:

• General turns digits into numbers and recognises dates.
• Text keeps values exactly as they are, including leading zeros in codes like 00123.
• Date lets you choose the order (MDY, DMY, YMD) so day and month aren't swapped.
• Do not import column (skip) leaves that part out.

Then set Destination. By default it is the first cell you selected, so the split starts in A2 and fills B and C. Choose B2 if you want to keep the original column. Click Finish.

The result:

Make sure Text to Columns won't overwrite data#

Text to Columns writes into the columns to the right of the destination. If those columns already hold data, Excel shows a warning asking whether to replace it. Click Cancel if you're not sure, then insert empty columns first or choose a different destination.

Count the parts before you start: three parts need three empty columns.

Fixed width: split by position#

Use Fixed width when the parts have no separator but always the same length. For a code like NYC2025A, the first 3 characters are the city and the next 4 the year.

In wizard step 1, choose Fixed width. In the next screen, click in the preview to add a break line, drag a line to move it, or double-click a line to remove it. Then set the formats and finish as before.

Other jobs Text to Columns does well#

Text to Columns is useful even when you don't want to split anything:

• Convert numbers stored as text. Select the column, open Text to Columns and click Finish straight away. Excel re-reads each cell as General, which turns text digits into numbers. See numbers stored as text for other fixes.
• Fix dates in the wrong order. If imported dates like 03/04/2025 were read with day and month swapped, run Text to Columns, choose Date in wizard step 3 and pick the order the data uses.
• Remove a part. Choose Do not import column to drop, for example, a prefix you don't need.

Text to Columns vs formulas#

Text to Columns is fast but static: change a name in column A and the split columns don't change. When the source data is refreshed regularly, use a formula instead.

Our guide to TEXTBEFORE and TEXTAFTER covers the newer functions. In older versions, LEFT with FIND works everywhere. Wrap the result in TRIM to remove the space that often follows a comma:

=TRIM(LEFT(A2,FIND(",",A2)-1))

For Sharma, Priya this returns Sharma. To handle middle names and other tricky cases, see how to split first and last names in Excel.

Clean the data before you split#

Messy data gives messy splits. Extra spaces create empty columns, and inconsistent separators split some rows differently from others. Clean the column first with TRIM and SUBSTITUTE, as shown in our guide to cleaning messy data. Once the parts are in their own columns, you can give them clear names or tidy formats; see named ranges and custom number formats for the next steps.

Practise it#

Try the formula in the exercise on this page, then practise splitting emails and names with TEXTBEFORE and TEXTAFTER in a live spreadsheet. The cleaning messy text lesson shows how to tidy spaces and separators before you split.