How to Split Text into Multiple Columns in Microsoft Excel (Text to Columns Wizard)

When importing data exports, CRM address books, or web downloads into Microsoft Excel, information often arrives lumped together into a single, congested column. A common scenario is having full names (first and last), complete mailing addresses (city, county, postcode), or date-and-time stamps squeezed into Column A. To sort, filter, or analyse this information effectively, you need each distinct data element separated into its own individual column.

While modern Excel versions offer formula functions like TEXTSPLIT, writing complex formulas across large datasets can be intimidating and can recalculate slowly. Excel’s built-in Convert Text to Columns Wizard provides a fast, permanent, and visual way to partition text across adjacent columns in just three steps. In this guide, we explore how to use the Text to Columns tool with delimited and fixed-width data, preserve leading zeros, and set custom destination ranges.

Delimited vs Fixed Width: Choosing the Right Method

When you launch the Text to Columns Wizard, Excel asks you to choose between two structural parsing types:

• Delimited: Choose this when your data elements are separated by consistent punctuation marks or spacing—such as commas, tabs, semicolons, hyphens, or single spaces. This is typical for CSV files, full names (“Jane Doe”), and email addresses.
• Fixed Width: Choose this when fields are aligned in vertical columns with spaces between each field, or when every record has an identical character length (such as an 8-character employee ID followed immediately by a 4-character department code).

Method 1: Splitting Delimited Text (Commas, Spaces, Tabs)

To split text separated by characters like commas or spaces (such as separating “Firstname Lastname” or “City, County”):

1. Highlight the column or cell range containing the text you want to split (e.g. cells A2:A100). Note: You can only select one column at a time.
2. Navigate to the Data tab on the Excel ribbon.
3. In the Data Tools section, click Text to Columns.
4. In Step 1 of the wizard, select Delimited, then click Next.
5. In Step 2, tick the checkbox corresponding to your separator:
   • Tick Comma for CSV data.
   • Tick Space for full names separated by a single space.
   • Tick Other and type custom characters (such as a forward slash /, vertical pipe |, or hyphen -).
6. Check the box labelled Treat consecutive delimiters as one if your data contains irregular double spaces or duplicate commas.
7. Review the Data preview box at the bottom of the dialogue window to verify that vertical break lines divide your words accurately.
8. Click Next to proceed to formatting, or click Finish to split immediately.

Method 2: Splitting Fixed-Width Columns

When processing legacy mainframe exports or fixed-width text files without punctuation delimiters:

1. Highlight the target column and click Data > Text to Columns.
2. In Step 1, select Fixed width and click Next.
3. In Step 2, you will see a graphical ruler above your data preview window. You control vertical break lines manually:
   • Create a break line: Click anywhere on the ruler at your desired character position.
   • Move a break line: Click and drag an existing vertical line left or right.
   • Delete a break line: Double-click directly on the vertical arrow line to remove it.
4. Once your vertical dividers are aligned with your data columns, click Next.

Preserving Leading Zeros on Phone Numbers and Postcodes

One of the most dangerous default behaviours in Excel is its automatic number formatting. When Excel sees a numeric sequence like 01234, it automatically converts it to a standard integer, stripping the leading zero and displaying 1234. For UK phone numbers, postcodes, bank sort codes, and serial numbers, this corrupts critical information.

You can prevent this in Step 3 of the Text to Columns Wizard:

1. In Step 3 of 3 of the wizard, click on the column preview in the bottom pane that contains the numbers with leading zeros.
2. In the Column data format section in the top-left corner, switch the radio button from General to Text.
3. The header over that preview column will change from General to Text.
4. Click Finish.

Excel will import the numbers strictly as literal strings, preserving every leading zero exactly as originally formatted.

Choosing a Custom Destination to Prevent Overwriting Data

By default, Excel sets the Destination field in Step 3 to the very first cell of your selected range (e.g. $A$2). This means Excel will overwrite your original source text in Column A with the first parsed word, and place subsequent parsed words into Column B and Column C. If Columns B and C already contain existing data, Excel will overwrite them without warning.

To keep your original source data intact and place the split results elsewhere:

1. In Step 3 of 3, locate the Destination: text box at the bottom.
2. Erase the default cell reference (e.g. $A$2) and click the cell where you want the new columns to begin (e.g. $D$2).
3. Click Finish.

Your original concatenated data will remain safe in Column A, while your newly separated fields populate Columns D, E, and beyond.

Troubleshooting Overwritten Data and Irregular Delimiters

• “There’s already data here. Do you want to replace it?” If this warning dialogue appears, your destination columns contain values. Click Cancel, insert sufficient blank columns to the right of your data, and re-run the wizard to avoid data destruction.
• Names with Suffixes or Middle Initials: When splitting full names using a space delimiter, individuals with middle names (e.g. “John Robert Smith”) or suffixes (“Jr.”, “III”) will split across three columns instead of two. Filter by blank cells in the third column to quickly identify and standardise multi-part names.
• Reversing Changes: If the text splits unexpectedly due to misaligned dividers, immediately press Ctrl + Z (or Command + Z on Mac) to restore your previous cell values.