Microsoft Excel comes with a Columns Wizard to split terms from a column into multiple columns using one or more patterns.
This is enough for simple use cases. But Excel will fall short with more advanced text-splitting needs.
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 Microsft Excel Columns Wizard would generate this result:
How to split text into columns/categories based on the values?
We want the result values into be inserted to corresponding columns.
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?
For performance reasons, Datablist analyzes the first 2000 items for your collection. The list of distinct terms must be present in those 2000 items. If other terms appear after the 2000 items, they will be ignored during the split process.
Are there any limitations on the number of columns created?
The text-to-column operation is performed in your browser. There is currently no limitation on the number of extracted terms. Please note that if you run this cleaning operation on incoherent data, you can end up with thousands of distinct terms. Resulting in performance issues.
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.