How to Use Text to Columns in Excel: Split Names, Dates and Codes

shape
shape
shape
shape
shape
shape
shape
shape
How to Use Text to Columns in Excel: Split Names, Dates and Codes - featured image

Text to Columns is an Excel feature that splits the content of one column into several columns. It is the fastest way to separate a full name into first and last names, split an address, break up data copied from a text file or a website, or convert text dates into real dates. Unlike a formula, it changes the cells directly, so it is best for one-time cleaning of a list. This guide shows both modes of the wizard, delimited and fixed width, with step by step examples, how to keep the original data, how to avoid overwriting neighboring cells, how to use it to fix dates and numbers, and what to do when you need a formula instead.

Updated November 2026

In this guide

  1. What Text to Columns does
  2. Example data
  3. Step 1: make space for the result
  4. Step 2: split names by a space
  5. Step 3: split codes by a hyphen
  6. Step 4: split CSV or tab data
  7. Step 5: split by fixed width
  8. Step 6: convert text dates to real dates
  9. Step 7: convert text numbers
  10. Text to Columns or formulas?
  11. Undo and recover
  12. Common problems
  13. Practice problems
  14. Quick recap

What Text to Columns does

The feature takes the text in the selected cells and cuts it at each separator, or at fixed positions, and puts the pieces in the neighboring columns to the right. You find it on the Data tab, in the Data Tools group. It works on one column at a time. The wizard has three steps: choose the type of split, set the separators or break lines, and choose the format and the destination of the new columns. The result is plain values, not formulas, so it will not update if the source changes.

Example data

Imagine a list of names in column A, such as Ali Khan, Sara Ahmed and Omar Malik, with a space between the first and the last name. You want first names in column A and last names in column B. Another list holds codes such as ABC-2026-0045, which you want to split into three parts at the hyphens. A third list has text like 2026/03/15 that you want to treat as a date. These three cases cover most uses of the feature.

Step 1: make space for the result

Text to Columns writes into the columns to the right of the selection, and it overwrites whatever is there without asking in some cases. Before you start, insert as many empty columns as you need. For a name with two parts, insert one blank column to the right. For a code with three parts, insert two. You can also set a different destination in step 3 of the wizard, which is safer. Always keep a copy of the original column, in case you need to undo the split later.

Step 2: split names by a space

Select the cells with the names, for example A2:A4. Go to Data, Text to Columns. In step 1, choose Delimited and click Next. In step 2, tick Space as the delimiter and untick the others. The preview shows the names split in two columns. Click Next, leave the format as General, and click Finish. Ali Khan becomes Ali in column A and Khan in column B. Sara Ahmed and Omar Malik split in the same way. The split took only a few seconds for three names, and it would take the same time for three thousand.

Step 3: split codes by a hyphen

Select the cells with the codes. In the wizard, choose Delimited and click Next. Tick Other and type a hyphen in the box. The preview shows three columns: ABC, 2026 and 0045. Be careful with the third column, since Excel may drop the leading zeros if the format is General, so 0045 becomes 45. To keep them, select that column in the preview in step 3 and choose Text as the column data format. Then click Finish, and the value stays as 0045.

Step 4: split CSV or tab data

When you copy data from a text file or a web page into one column, each row may contain commas or tabs between the values. Select the column, run the wizard and tick Comma or Tab. If some values contain commas inside quotation marks, such as a company name with a comma, set the Text qualifier to the double quote, so that Excel does not split inside them. The same wizard handles semicolons, which many European systems use. You can tick more than one delimiter, and tick Treat consecutive delimiters as one to avoid empty columns when there are double spaces.

Step 5: split by fixed width

Some old systems export data in fixed columns, where each field has a set number of characters. In step 1, choose Fixed width. In step 2, click in the preview to set break lines at the places where the fields start, and drag a line to move it, or double-click to remove it. For example, a code of 12 characters could be split into 3 letters, 4 digits and 5 characters at positions 3 and 7. Click Next and Finish. This mode also works for splitting a long number, such as a date written as 20260315, into 2026, 03 and 15.

Step 6: convert text dates to real dates

Sometimes dates are text and cannot be sorted or used in formulas, such as 2026/03/15 or 15.03.2026. Select the cells, start the wizard, choose Delimited, click Next and untick all separators, then click Next. In step 3, choose Date and pick the order, such as YMD for year, month and day. Click Finish, and Excel converts the text to real dates. Format the cells as dates afterwards. This trick also fixes dates that appear left-aligned. Our text to number guide shows other ways to fix text that looks like numbers.

Step 7: convert text numbers

A similar trick converts numbers stored as text. Select the column, run Text to Columns, click Finish at once, without changing anything. Excel re-reads the values and turns text that looks like numbers into real numbers. It is a quick fix for numbers with a green triangle in the corner of the cell. If the numbers have a trailing minus sign or a currency symbol, set the format in step 3 as needed. After the conversion, SUM and sorting work as expected.

Text to Columns or formulas?

Text to Columns is quick, but it is a one-time action. If the source data changes often, a formula updates automatically. Use LEFT, MID and RIGHT, as explained in our LEFT, RIGHT and MID guide, or TEXTSPLIT in Excel 365. Flash Fill is a third option: type the first result by hand, press Ctrl+E, and Excel fills the rest by pattern. For a large list that you clean once, Text to Columns is the best choice. For a report that is refreshed every week, use formulas or Power Query.

Undo and recover

If the split goes wrong, press Ctrl+Z at once. If you have already done other actions, you may have to restore from the copy of the original column. This is why a copy is so important. If the pieces went into columns that had data, the data is lost unless you undo. Another good habit is to run the wizard on a copy of the sheet first, check the result and then repeat on the real data. Save the file before big changes, so you can go back to an earlier version.

Common problems

The most common problem is that leading zeros disappear, which you fix by setting the column to Text. Another is that names with middle names split into three columns, so the last name moves. Check the preview and handle such rows by hand, or use formulas. A third problem is data with several spaces between words, so use TRIM first, as shown in our remove spaces guide. A fourth is overwritten data, so insert columns first. A fifth is the wrong delimiter, such as a semicolon in a file that uses commas.

Practice problems

Try these. Problem 1: split Ali Khan with a space, and the result is Ali and Khan. Problem 2: split ABC-2026-0045 with a hyphen, and the result is three parts: ABC, 2026 and 0045. Problem 3: split 20260315 by fixed width at positions 4 and 6, and the result is 2026, 03 and 15. Problem 4: convert 2026/03/15 to a date using the Date format with YMD. Problem 5: split a list of city and country pairs with a comma, and keep the original column.

Quick recap

Select the column, open Data, Text to Columns and choose Delimited or Fixed width. Set the separators or the break lines, then choose the data format for each new column, using Text to keep leading zeros and Date to fix dates. Insert empty columns first, or set a different destination, and keep a copy of the original. Use formulas or Flash Fill when the data changes often. Text to Columns is a fast, simple tool that saves hours when you clean imported lists.

Frequently asked questions

Where is Text to Columns in Excel?

On the Data tab, in the Data Tools group.

How do I split a full name into first and last name?

Use Text to Columns with a space as the delimiter.

How do I keep leading zeros?

Set the column data format to Text in step 3 of the wizard.

Can I undo Text to Columns?

Yes, press Ctrl+Z straight away.

What is the difference between delimited and fixed width?

Delimited splits at separators, and fixed width splits at set positions.

Related guides and free tools

Leave a Reply

Your email address will not be published. Required fields are marked *