Split Columns in Excel: Beginner’s Step-by-Step Guide

This guide shows you how to split one column of data into two or more columns in Microsoft Excel. You’ll learn to separate text like full names into first and last names, or split dates and numbers using Excel’s built-in tools. By the end, you’ll be able to clean up messy data in just a few clicks.

What You’ll Need

• Microsoft Excel (2013 or later, including Microsoft 365)
• A spreadsheet with a single column containing data you want to split (e.g., “John Smith” in one cell)
• An empty column next to your data where the split results will appear (Excel will overwrite adjacent cells, so make sure there’s space)

Step-by-Step

1. Open your Excel file and locate the column with the data you want to split. For example, column A might contain full names like “John Smith”.
2. Select the entire column or the specific cells you want to split. Click the letter at the top of the column (e.g., “A”) to select the whole column, or click and drag to select a range of cells.
3. Go to the “Data” tab in the ribbon at the top of Excel. It’s between “Formulas” and “Review” in most versions.
4. Click “Text to Columns” in the “Data Tools” group. A wizard will open.
5. Choose the file type that best describes your data:
   • Delimited: Choose this if your data is separated by a character like a comma, semicolon, tab, or space. For example, “John,Smith” or “John Smith”.
   • Fixed width: Choose this if your data has a consistent number of characters in each part, like “JOHN SMITH” with spaces aligning columns. This is less common for beginners.

   Click Next.

6. Set your delimiters (if you chose “Delimited”):
   • Check the box next to the delimiter that matches your data. For names, check “Space”. For CSV data, check “Comma”. For other separators, check “Other” and type the character (e.g., a semicolon or a pipe |).
   • You can check multiple delimiters if needed (e.g., both comma and space).
   • Look at the “Data preview” at the bottom to see how your data will be split. If it looks wrong, adjust the delimiters.
   • Click Next.
7. Format the split columns (optional): In this step, you can set the data format for each new column. The default “General” works fine for most text and numbers. If you’re splitting dates, select the column in the preview and choose “Date” with the correct format. Click Finish.
8. Check the results: Excel will replace the original column with the split columns. For example, if you split “John Smith” by space, column A will now contain “John” and column B will contain “Smith”. If you need to keep the original column, copy it to another column first before splitting.
9. Adjust column widths if needed: After splitting, the new columns might be too narrow. Select the columns, then double-click the boundary between column letters to auto-fit the width.

Common Mistakes

• Not leaving empty columns: Text to Columns overwrites the cells to the right. If there’s data in those cells, it will be replaced without warning. Always ensure you have enough empty columns.
• Choosing the wrong delimiter: If your data uses spaces but you select “Comma”, nothing will split. Check the delimiter carefully.
• Using “Fixed width” for irregular data: Fixed width splits at exact character positions. If your data varies in length (e.g., “John Smith” vs “Alex Johnson”), use “Delimited” instead.
• Forgetting to select the entire column: If you only select a few cells, Excel will split only those, leaving the rest of the column untouched, which can cause misalignment.
• Overwriting the original data: Once split, the original column is gone. If you need the original, copy it to a new column first.

Tips

• Use Flash Fill for simple splits: If you’re splitting names or similar patterns, you can type the first part in the adjacent cell (e.g., type “John” in B1), then press Ctrl+E to auto-fill the rest. This is faster for one-off splits but less reliable for complex data.
• Preview before finishing: The wizard’s preview pane shows exactly how your data will look. Use it to catch errors before applying.
• Split into multiple columns: You can split into more than two columns. For example, “John Michael Smith” split by space gives three columns.
• Use “Text to Columns” for dates: If you have dates like “01/02/2023” and want to separate month, day, and year, use the “Delimited” option with “/” as the delimiter, or use the “Date” format in step 3 of the wizard.
• Combine with TRIM: If your data has extra spaces (e.g., “John Smith”), use the TRIM function first to clean it up, then split.

Troubleshooting

• Nothing happens when I click “Text to Columns”: Make sure you have selected at least one cell. If the button is greyed out, your sheet might be protected. Go to Review > Unprotect Sheet.
• The split doesn’t separate my data: Check that your delimiter is correct. For example, if your data uses a semicolon but you selected comma, it won’t split. Also, ensure there are no leading/trailing spaces that might confuse the split.
• I get an error about “Text to Columns” not working on merged cells: Unmerge any merged cells in the selected range first. Merged cells are incompatible with this feature.
• My data has multiple spaces or inconsistent separators: Use the “Treat consecutive delimiters as one” checkbox in the delimiter step. This treats “John Smith” (multiple spaces) as “John Smith” (one space).
• The split creates extra empty columns: This happens when there are trailing delimiters. For example, “John Smith,” with a comma at the end will create an empty column. Check your data for extra characters and clean it up first.

Frequently Asked Questions

Can I split a column without losing the original data?

Yes. Before using Text to Columns, copy the column and paste it to a new location (e.g., column C). Then split the copied column. The original remains intact.

What’s the difference between “Delimited” and “Fixed width”?

Delimited splits data based on a character like a comma or space. Fixed width splits at exact character positions. Use Delimited for most text data; use Fixed width only when your data is aligned in columns with consistent spacing.

Can I split a column into more than two columns?

Yes. Text to Columns can split into as many columns as there are delimiters. For example, “John Michael Smith” split by space gives three columns.

Why is my “Text to Columns” button greyed out?

This usually means the sheet is protected. Go to the Review tab and click “Unprotect Sheet”. If you don’t know the password, you’ll need to get it from the sheet owner.

Can I undo a split?

Yes, press Ctrl+Z immediately after the split to undo it. However, if you’ve made other changes after the split, the undo might not revert it. Always save a backup copy before splitting.

How do I split a column by line break (e.g., addresses with multiple lines)?

In the delimiter step, check “Other” and press Ctrl+J (which represents a line break). This will split at each new line within a cell.

Related

• Calculate Months Between Two Dates in Excel
• 7 Days to Die Same Key Already Exists Error Fix
• Outlook View Folder Size: How to Check It