How to Split One Column into Multiple in Excel

This guide shows you how to take data from a single column in Excel and split it into multiple columns. This is useful when you have full names, addresses, dates, or other combined data that you need to separate for sorting, filtering, or analysis. You’ll learn the built-in Text to Columns feature, Flash Fill, and a formula-based method, with step-by-step instructions for each.

What You’ll Need

Microsoft Excel (2013 or later is recommended; the steps are similar in older versions).
A worksheet with the data you want to split, in a single column.
An empty column (or columns) to the right of your data, where the split results will go.

Step-by-Step

There are three main ways to split a column in Excel. The method you choose depends on your data and whether you want a one-time split or a dynamic formula.

Method 1: Text to Columns (for one-time splitting)

Select the column that contains the data you want to split. Click the letter at the top of the column to select the entire column, or select just the range of cells you need.
Go to the Data tab on the ribbon. In the ‘Data Tools’ group, click Text to Columns.
Choose the data type. In the wizard, select either Delimited (if your data is separated by commas, spaces, tabs, etc.) or Fixed width (if your data has a consistent number of characters, like a code). Click Next.
Set your delimiters. If you chose Delimited, check the box next to the delimiter that matches your data (e.g., Comma, Space, Tab). You can also check ‘Treat consecutive delimiters as one’ if you have extra spaces. If you chose Fixed width, set the break lines by clicking in the preview to add vertical lines where you want the splits. Click Next.
Choose the destination. In the ‘Destination’ field, click the icon at the end of the box, then click on the cell where you want the first piece of data to appear. This is usually the top cell of the next empty column to the right. Click Finish.
Check the results. Your original column will be replaced with the split data. If you wanted to keep the original, copy the column to a new location first, or use a different method.

Method 2: Flash Fill (for quick, pattern-based splitting)

Add a new column to the right of your data. Type the first piece of the split data manually in the first cell of the new column.
Start typing the second row. As you type, Excel will try to detect the pattern and show a preview of the rest of the column in a lighter gray.
Press Enter to accept the Flash Fill suggestion. If you don’t see a suggestion, press Ctrl+E (or Cmd+E on Mac) to trigger Flash Fill manually.
Repeat for each piece of data you want to split into its own column. For example, for a full name, you’d do this once for the first name and once for the last name.

Method 3: Formulas (for dynamic splitting that updates automatically)

Identify the delimiter that separates your data (e.g., comma, space, hyphen).
Use the LEFT, RIGHT, and MID functions along with FIND or SEARCH to extract parts. For example, to get the first word before a space: =LEFT(A2, FIND(" ", A2) - 1).
For the second part, use a combination like: =MID(A2, FIND(" ", A2) + 1, FIND(" ", A2, FIND(" ", A2) + 1) - FIND(" ", A2) - 1). This gets the text between the first and second spaces.
For the last part, use: =RIGHT(A2, LEN(A2) - FIND(" ", A2, FIND(" ", A2) + 1)).
Copy the formulas down for all rows. These formulas will update automatically if you change the original data.

Common Mistakes

Not leaving enough empty columns. If you don’t have empty columns to the right, Excel will overwrite existing data when using Text to Columns.
Choosing the wrong delimiter. If your data uses a comma and a space, but you only select comma, you’ll get extra spaces at the start of the second column.
Using Text to Columns on a column that has formulas. This will replace the formulas with static text, which may break your spreadsheet.
Forgetting to copy the original data. Text to Columns overwrites the original column. If you need to keep it, copy it to a new column first.

Tips

Use ‘Treat consecutive delimiters as one’ when you have inconsistent spacing (e.g., “John, Doe” with two spaces).
Preview the data in the Text to Columns wizard before clicking Finish. It shows you exactly how the split will look.
For dates split into day, month, year, use the DATE function with the split parts, or use Text to Columns with the Date option and choose the correct format.
If you need to split by a line break (e.g., an address with multiple lines), use the delimiter ‘Other’ and press Ctrl+J (or Cmd+J on Mac) to enter a line break character.
Use Flash Fill for simple patterns—it’s often faster than formulas and doesn’t require knowing the exact function syntax.

Troubleshooting

Flash Fill isn’t working. Make sure you have a clear pattern. If you have a header row, include it in your selection. Also, ensure that there are no blank rows that break the pattern. Try selecting the cell where you typed the first value, then press Ctrl+E.
Text to Columns gives an error. This usually happens when the destination column is not empty or is merged. Unmerge any cells in the destination area, or choose a different destination cell.
The split data is not aligned correctly. This often occurs with fixed-width splitting when the data has variable lengths. Use Delimited instead, or adjust the break lines carefully in the wizard.
Formulas return #VALUE! errors. This means the delimiter was not found in the cell. Check that your formula references the correct delimiter and that the cell actually contains that character. Use IFERROR to handle cases where the delimiter is missing.
I need to split into more columns than the wizard allows. Text to Columns can handle up to 255 columns, which should be enough for most cases. If you need more, consider using a formula or a Power Query split.

Frequently Asked Questions

Can I split a column without overwriting the original?

Yes. Before using Text to Columns, copy the original column to a new location (e.g., paste it into the next empty column). Then run Text to Columns on the copy. Alternatively, use formulas or Flash Fill to put the split results in new columns while keeping the original intact.

How do I split by a custom delimiter like a semicolon?

In the Text to Columns wizard, select ‘Delimited’ and then check the ‘Other’ box. Type your custom delimiter (e.g., 😉 in the box next to ‘Other’. You can also use this for other characters like a pipe (|).

What if my data has multiple delimiters, like “Name, Age, City”?

You can split into three columns by using Text to Columns with the comma delimiter. The wizard will create three columns. If you have a mix of delimiters (e.g., commas and spaces), check both ‘Comma’ and ‘Space’ and consider ‘Treat consecutive delimiters as one’ to tidy up.

Can I split a column into rows instead of columns?

Yes, but it’s a different process. You can use the ‘Transpose’ feature after splitting into columns, or use Power Query’s ‘Split Column by Delimiter’ and then ‘Unpivot’ to get rows. For a quick method, copy the split columns, right-click, and choose ‘Paste Special’ > ‘Transpose’ to flip them into rows.

Why is my Flash Fill not detecting the pattern?

Flash Fill works best with consistent patterns. If your data has variations (e.g., some names have middle initials, some don’t), it may fail. Try providing a few more examples manually, or use Text to Columns with a delimiter for a more reliable result.

Is there a way to split a column in Google Sheets that also works in Excel?

Yes, the basic methods are similar. Google Sheets has a ‘Split text to columns’ option under Data, which works like Text to Columns. Formulas like SPLIT in Google Sheets are different, but you can use the same LEFT, MID, RIGHT functions in both. For a dynamic split, consider using Power Query in Excel (Get & Transform) which is more powerful and also available in Google Sheets via add-ons.

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →