To split a column into one named column per distinct value, use Datablist's Split Property tool with One term per property enabled. For a simple split by position, Excel's Text to Columns wizard is enough.
Text to Columns puts the first term in the first output column, the second term in the second, and so on. It does not group the same term into the same named column when terms appear in different positions.
For example, if you need to split text and have the same terms to be moved to the same columns.
We want to split the text so each column gets a distinct term:
In this use case, splitting the column with Excel's Text to Columns wizard would generate this result:
How to split text into columns/categories based on the values?
We want each value in its corresponding named column, regardless of its position in the original cell.
To perform this splitting operation, you have to use Datablist. Datablist is the perfect tool to perform this data-cleaning task.
Datablist is an online tool. Just click here to open Datablist.
Import your Excel or CSV file directly from the "getting started" page. Or create a new collection and follow the import wizard.
Once your data is imported into Datablist. Open the "Split Property" tool from the "Edit" menu.
The tool lets you define the property with the text to be split. You can define one or more delimiters.
Delimiters can be one of the built-in patterns (comma, semi-colon, tab, etc.) or a custom pattern.
For this use case, where we want the text to be split into specific columns, open the "More settings" panel and enable the "One term per property" setting.
Then, click on "Analyse data".
Datablist will scan your data to extract the distinct terms. The process is case-insensitive, and extra leading and trailing spaces around terms will be removed.
If the analysis results are consistent with the expected columns, click on "Preview Split".
A preview split shows the expected result after the text-splitting process. Click "Split property" to split the data.
And the data is split.
Click "Export" to export the results into a CSV or Excel file.
FAQ
Can I split texts into columns with a custom pattern?
Yes. Datablist handles custom patterns. A custom pattern is matched as a whole. You can match several custom patterns using the multiple delimiters feature.
What happens to leading or trailing spaces when splitting text into columns?
Datablist removes all leading and trailing spaces during the process. It also compares the terms without the case. The final case for each term is the one from the first match.
How many items are analyzed to generate the list of distinct terms?
Datablist analyzes up to the first 3,000 items in the selected scope to determine which term columns to create. If a term appears only after that sample, the split cannot place it in a column that was never created. Check the analysis and preview before running the split, or start with a representative subset that includes every expected term.
Are there any limitations on the number of columns created?
The operation runs in your browser. A column is created for each distinct term found during analysis, so inconsistent spelling or free-form text can create far more columns than you expect. Normalize the values first and review the proposed columns before running the split.
How do I split text into multiple columns with a CSV file?
Datablist opens Excel and CSV files. And to be honest, Datablist is the best online CSV editor on the market. The text-to-columns tool is also available with CSV files.