How to Split Excel Data Using a Delimiter or Formula
Question details
The user needs to split a single column containing multiple values separated by specific characters into separate columns.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing imported or messy data by separating text strings into distinct columns based on delimiters or complex patterns.
- Observed behavior
- Data is trapped in a single column with inconsistent separators or complex patterns, preventing proper data analysis and formatting.
Before splitting your data, ensure you insert enough empty columns to the right of your target column so that the newly split data does not overwrite your existing data.
Split Data Using the Text to Columns Feature
This built-in tool is the easiest way to separate text into different columns based on a specific delimiter like a comma, space, or custom character.
Text to Columns is the standard method for splitting data when you have a consistent separator between values. It parses the data and distributes it across adjacent cells.
Highlight the column or cells containing the text you want to split.
Go to the "Data" tab on the Excel ribbon and click the "Text to Columns" button.
In the wizard, select "Delimited" if your data uses characters like commas or tabs to separate fields, then click "Next".
Check the box for your specific delimiter (e.g., Comma, Space, or check 'Other' and type a custom character). The Data Preview window will show how it splits. Click "Finish".
Split Data Using Dynamic Formulas
If you are using Microsoft 365, you can use dynamic array formulas to split data without altering the original text, which is especially useful for automated workflows.
Standardize and Split Complex Data with Power Query
For data with complex or inconsistent delimiters, you can standardize the separators first, then use Power Query to split the columns efficiently.
Split and Manage Data Easily with WPS Spreadsheet
WPS Spreadsheet offers powerful, built-in tools like Text to Columns, advanced formulas, and intuitive data processing features to split and organize complex datasets seamlessly.
- 1. Open your file: Launch WPS Spreadsheet and open the workbook containing the combined data.
- 2. Select the data: Highlight the column or specific cells you need to split.
- 3. Use Text to Columns: Navigate to the "Data" tab on the top menu and click on "Text to Columns".
- 4. Configure the split: Select "Delimited", choose the specific character separating your data (like a comma or space), and click "Finish" to separate your data instantly.
Frequently Asked Questions
Why is Text to Columns overwriting my adjacent data?
Text to Columns places the newly split data into the columns immediately to the right of your original data. If those columns contain existing data, it will be overwritten. Always insert blank columns before performing the split.
Can I split data based on a line break?
Yes. In the Text to Columns wizard, select 'Other' as your delimiter and press Ctrl+J in the text box next to it. This shortcut represents the line break character, allowing you to split multi-line cell content.
What should I do if my delimiters are inconsistent?
You can first use the SUBSTITUTE function or the Find and Replace tool (Ctrl+H) to standardize all separators into a single character (like a comma or semicolon) before using the Text to Columns feature or Power Query.
Is TEXTSPLIT available in all versions of Excel?
No, the TEXTSPLIT function is a dynamic array function available only in Microsoft 365 and Excel for the web. Older versions should rely on the Text to Columns feature or a combination of LEFT, MID, and RIGHT functions.
