Summary

One column holding Smith, John or 14 Mill Road, Bristol, BS1 4TR is fine to read and impossible to sort, filter, or look up. This article shows you how to split it with Text to Columns, how to stop the wizard from eating the columns next to it, how to keep leading zeros and product codes intact, and when a TEXTSPLIT formula is the better tool.

In this guide

• 1. Insert Blank Columns Before You Split
• 2. Split a Column by a Delimiter
• 3. Set Each Column's Data Format Before You Finish
• 4. Split a "Last, First" Name Column
• 5. Split a Full Address into Separate Columns
• 6. Split by Fixed Width When There Is No Delimiter
• 7. Use TEXTSPLIT in Microsoft 365

Before You Start

• Excel for Windows or Excel for Mac. Excel for the web has no Text to Columns command — use TEXTSPLIT there, or open the file in the desktop app.
• Data in a single column, with a consistent separator or a consistent character position.
• Edit permission on the workbook. No admin rights required.

Instructions

The examples below use a sheet with names in column A and nothing in columns B onward.

1. Insert Blank Columns Before You Split

This is the step people discover after they have lost a column of data. Text to Columns writes its results into the cells to the right of your source column and overwrites them.

1. Count how many pieces your split will produce. Smith, John produces two, so you need one blank column beside the original.
2. Right-click the column letter to the right of your data — column B if your data is in A — and choose Insert. A new empty column appears and everything shifts right.
3. Repeat for each extra piece. A four-part address needs three blank columns.

Tip: There is a second route that leaves your sheet untouched. On Step 3 of the wizard, clear the Destination box and click an empty cell well clear of your data — $H$1, for example. The split lands there instead of over the top of your original neighbours.

Note: If the wizard warns that there is already data in the destination cells, stop and click Cancel. That message means the split is about to overwrite something. Add the blank columns and start again.

2. Split a Column by a Delimiter

A delimiter is the character that separates the values — a comma, a space, a tab, a semicolon, or something specific to your system such as a pipe.

1. Select the source column by clicking its column letter. Select one column only; the wizard handles a single column at a time.
2. Click the Data tab on the ribbon.
3. In the Data Tools group, click Text to Columns. The Convert Text to Columns Wizard - Step 1 of 3 window opens.
4. Select Delimited and click Next.
5. On Step 2 of 3, tick the box for your separator under Delimiters: Tab, Semicolon, Comma, Space, or Other. For Other, type the character into the small box beside it.
6. Watch the Data preview pane at the bottom of the window. Vertical lines appear showing exactly where each split will fall. If the lines are in the wrong places, change the ticked delimiter until they are right.
7. Click Next, then Finish.

Verification: Your single column is now several columns, each holding one piece of the original value. The source column keeps the first piece.

Tip: If your data uses two separators at once — a comma and a space, as in Smith, John — tick both Comma and Space, then tick Treat consecutive delimiters as one so the space after the comma does not create an empty column.

3. Set Each Column's Data Format Before You Finish

Step 3 of 3 is the step everyone clicks past, and it is the one that protects your data. By default every resulting column is set to General, which means Excel reads each value and decides what it is — turning 02134 into 2134 and 03-05 into 3-May.

1. Get to Step 3 of 3 of the wizard by clicking Next twice.
2. In the Data preview pane, click the heading bar above a column to select it. The selected column turns black.
3. Under Column data format on the right, choose the option that fits:
   • General — for anything you will do arithmetic on. Numbers stay numbers, dates stay dates.
   • Text — for ZIP codes, phone numbers, product codes, employee IDs, and anything with a leading zero. This is the setting that stops the conversion.
   • Date — for real dates, with a dropdown beside it for the order the source uses, such as MDY or DMY.
   • Do not import column (skip) — to throw that piece away instead of writing it to the sheet.
4. Repeat for every column that needs something other than General.
5. Click Finish.

Verification: Click a cell in a column you set to Text. The value sits against the left edge of the cell rather than the right — that is Excel showing you it is holding text, not a number.

Note: Text format means the column no longer calculates. =SUM() across it returns 0. Use it for identifiers, never for figures. The full picture on why Excel rewrites values on entry is in How to Stop Excel Changing Numbers into Dates.

4. Split a "Last, First" Name Column

A column of Smith, John sorts correctly but cannot be used in a mail merge or matched against another list.

1. Insert one blank column to the right of the name column, following Step 1.
2. Select the name column and click Data > Text to Columns.
3. Select Delimited and click Next.
4. Tick Comma and Space, then tick Treat consecutive delimiters as one. The Data preview now shows two clean columns with no empty one between them.
5. Click Next. On Step 3 of 3, leave both columns as General — names carry no leading zeros to protect.
6. Click Finish. Column A holds surnames, column B holds first names.

Tip: Add a header to each new column straight away — Last Name and First Name. A split leaves the new columns unlabelled, and an unlabelled column breaks filtering later. See How to Sort and Filter Data in Excel.

Note: Middle names and suffixes break this. Smith, John A. with Space ticked produces three columns, and the third one is populated for some rows and empty for others. For messy name data, How to Use Flash Fill in Excel copes better, because you show it the answer you want rather than describing a rule.

5. Split a Full Address into Separate Columns

14 Mill Road, Springfield, IL, 62704 needs to become four columns, and the last one is the one that gets damaged.

1. Insert three blank columns to the right of the address column.
2. Select the address column and click Data > Text to Columns > Delimited > Next.
3. Tick Comma only. Leave Space unticked — street names and city names contain spaces, and ticking it shatters them into a column each.
4. Tick Treat consecutive delimiters as one to absorb the space that follows each comma.
5. Click Next. On Step 3 of 3, click the heading of the final column in the Data preview and select Text under Column data format. This keeps 62704 as five characters and preserves the leading zero in a ZIP such as 02134.
6. Click Finish.

Verification: Each address is spread across four columns, and the ZIP column shows every digit it started with, aligned to the left of its cells.

Note: Addresses with an apartment or suite line hold an extra comma, so those rows produce five pieces and push the ZIP one column further right. Sort by the last column before you split to group the irregular rows together, fix them by hand, then split the rest.

Tip: Where the address is for reading rather than filtering or mail merge, leave it in one cell and make it legible instead — How to Wrap Text and Merge Cells in Excel covers wrapping and manual line breaks.

6. Split by Fixed Width When There Is No Delimiter

Some exports have no separator at all — every value starts at the same character position, padded with spaces. Mainframe reports and older banking downloads look like this.

1. Select the column and click Data > Text to Columns.
2. Select Fixed width and click Next.
3. The Data preview pane on Step 2 of 3 shows your data under a ruler. Click anywhere in the preview to place a break line where you want a split. A vertical line with an arrow appears.
4. Adjust as needed:
   • Drag a break line to move it.
   • Double-click a break line to delete it.
   • Click again to add another.
5. Click Next, set the format of each column as described in Step 3, and click Finish.

Tip: Widen the source column before you start the wizard. The preview inherits the on-screen width, and a narrow column makes the break lines fiddly to place.

7. Use TEXTSPLIT in Microsoft 365

Text to Columns replaces your data. TEXTSPLIT leaves the original where it is and produces the split alongside it, recalculating whenever the source changes.

1. Click an empty cell to the right of your data — cell C2 if your value is in A2.
2. Type the formula, using the separator inside quote marks:

=TEXTSPLIT(A2, ", ")

3. Press Enter. The result spills across the cells to the right, one piece per cell, outlined with a thin blue border.
4. Drag the small square at the bottom-right corner of the cell down the column to split every row.

Useful variations:

• Split on a comma and clean up the spaces separately: =TRIM(TEXTSPLIT(A2, ",")) removes any space left at the start of each piece.
• Split down a column instead of across: =TEXTSPLIT(A2, , ", ") uses the third argument, which splits into rows.
• Ignore empty pieces: =TEXTSPLIT(A2, ",", , TRUE) drops the blanks caused by two separators in a row.

Note: TEXTSPLIT exists in Microsoft 365 and Excel for the web. In Excel 2021 and earlier it returns #NAME?, and anyone opening your workbook in an older version sees that error too. Use Text to Columns for files you share across mixed versions.

Tip: A #SPILL! result means something is sitting in the cells the formula needs. Clear them and the result appears. More on Excel's error messages: How to Fix Common Excel Formula Errors.

Troubleshooting

WARNING

Text to Columns overwrites the columns to the right without a second chance. The wizard shows one confirmation about replacing destination cells, and clicking OK on it destroys whatever was there. If you have already lost data, press Ctrl + Z immediately — undo restores it. Once the workbook has been saved and closed, it is gone.

Symptom / Error	Potential Cause	Solution
The columns beside my data are now empty	Wizard overwrote them	Press Ctrl + Z to undo. Insert blank columns first, or set a Destination cell away from your data — see Step 1.
Leading zeros disappeared	Column left on General format	Undo, run the wizard again, and set that column to Text on Step 3 of 3 — see Step 3.
Product codes turned into dates	General format read 03-05 as a date	Set the column to Text on Step 3 of 3. The value is unrecoverable once converted, so undo rather than reformat.
Everything stayed in one column	Wrong delimiter ticked	Check the Data preview on Step 2 of 3 — no vertical lines means the character you ticked is not in your data. Try Other and type the exact character.
Empty columns between the results	Two delimiters in a row	Tick Treat consecutive delimiters as one on Step 2 of 3.
Text to Columns is greyed out	More than one column selected, or the sheet is protected	Select a single column. If it stays greyed out, the sheet is protected — tell IT or the file owner: "Please unprotect this worksheet so I can run Text to Columns."
Everything I paste now splits itself	Excel reuses the last wizard settings	Run Text to Columns on any single cell, untick every delimiter on Step 2 of 3, and click Finish to reset it.

Related Articles

• How to Use Flash Fill in Excel
• How to Stop Excel Changing Numbers into Dates
• How to Wrap Text and Merge Cells in Excel
• How to Remove Duplicate Rows in Excel
• How to Sort and Filter Data in Excel
• How to Fix Common Excel Formula Errors
• Understanding Excel Features — A Quick-Reference Guide to What Excel Can Do

Did this solve your problem?