How to Split Text Into Columns in Excel (Step-by-Step Guide)
You have full names, combined addresses, or raw data trapped in single cells (e.g., "John Smith, 123 Main St, New York") and need to separate them into distinct columns for First Name, Last Name, Address, and City.
To split text into columns in Excel: Select the column containing your data, go to the Data tab on the ribbon, and click "Text to Columns". Choose "Delimited" (for commas, tabs, spaces, or semicolons) or "Fixed width" (for character-aligned reports). In step 2, check the box matching your separator. In step 3, select destination cells and set column data formats (choose "Text" for IDs with leading zeros). Finally, click "Finish". Alternatively, in modern Microsoft 365, you can use the formula `=TEXTSPLIT(A1, ",")` to split text dynamically.
In This Guide
1. Why Does This Problem Happen? (Root Causes)
Unstructured Data Export: Many CRM and legacy accounting systems export contact records as concatenated single-line strings.
Accidental Single-Column Pasting: Pasting text without tab separators causes Excel to dump all content into Column A.
Inconsistent Delimiters: Records that use commas in some rows and spaces or dashes in others.
2. How to Fix It (Step-by-Step Methods)
Step 1: Check for Overwrite Risk
Before running Text to Columns, inspect how many columns your data will split into. If you have data in Column B and C, Text to Columns will overwrite it without asking! Insert 3–5 blank columns to the right of your source data to protect existing information.
Step 2: Launch the Text to Columns Wizard
Highlight your data column. Navigate to Data tab → Text to Columns. Choose "Delimited" and click Next.
Step 3: Select Delimiters & Consecutive Delimiter Rules
Check the delimiter present in your text (e.g. Comma, Space, or Other). If your data contains multiple consecutive spaces (visual padding), check "Treat consecutive delimiters as one". Preview the columns in the Data preview box.
Step 4: Set Column Data Format & Finish
Click on individual column headers in the preview. If a column contains IDs or phone numbers, select the "Text" radio button so Excel doesn't strip leading zeroes or apply scientific notation. Click Finish.
3. Verified Test Fixture & Output
Reproducible test input and the verified multi-column result.
FullName,Location,Department Sarah Connor,Los Angeles - CA,Cybernetics Kyle Reese,Dallas - TX,Security John Connor,San Francisco - CA,Operations
| FullName | Location | Department |
|---|---|---|
| Sarah Connor | Los Angeles - CA | Cybernetics |
| Kyle Reese | Dallas - TX | Security |
| John Connor | San Francisco - CA | Operations |
4. Common Pitfalls to Avoid
5. Frequently Asked Questions
What is the formula equivalent to Text to Columns in modern Excel?
In Microsoft 365 and Excel for the Web, you can use `=TEXTSPLIT(A1, ",")` to split text across columns, or `=TEXTSPLIT(A1, ",", ";")` to split across both columns and rows simultaneously.
Can Flash Fill split text without the wizard?
Yes. Type the desired split value in the adjacent cell (e.g., type "John" in B1 next to "John Smith" in A1), press Enter, and press Ctrl+E (Flash Fill). Excel will detect the pattern and fill down the entire column.
Related Guides & Tutorials
How to Fix CSV Files Opening in One Column in Excel
Fix CSV files opening in a single column in Excel. Use Text to Columns, Power Query, regional separator fixes, or our free converter without data loss.
Step-by-Step TutorialHow to Convert Fixed-Width Text Into a Spreadsheet
Step-by-step instructions to convert fixed-width, column-aligned text, and mainframe reports into structured Excel tables and CSVs.
Authoritative Technical References & Standards
- • IETF RFC 4180 Specification: Common Format and MIME Type for Comma-Separated Values (CSV) Files.
- • Microsoft Excel Documentation: Import or export text (.txt or .csv) files in Microsoft Excel.
- • Unicode Consortium Standards: UTF-8, UTF-16, and Byte Order Mark (BOM) Specifications.