Introduction
Hi, I am Akira, your guide at Data Without Code. First of all, congratulations! If you have followed our tutorials up to this point, you have officially graduated from our KNIME Basics module. Your workspace is set up, your workflows are safe, and you are ready for some serious data engineering.
Welcome back to the Data Prep & ETL module. Today, we are going to tackle one of the most common and annoying tasks in data cleaning: Splitting one column of text into multiple columns.
In Excel, you probably do this using the manual “Text to Columns” wizard. Maybe you need to split a “Full Name” column into “First Name” and “Last Name” using a space, or extract the company domain from an email address using the “@” symbol.
Doing this manually every week is a massive waste of your time. In this tutorial, I will show you how to automate this exact process in seconds using KNIME’s Cell Splitter node.
Meet the Cell Splitter Node
When you export data from a CRM, a web form, or a legacy system, it often comes out mashed together. For example, a single cell might contain "Tokyo, Japan, 100-0001".
To analyze this data properly, we need to break it apart. In KNIME, we do this using the Cell Splitter node. It looks at a specific character—called a delimiter (like a comma, a space, or a dash)—and uses it like a pair of scissors to cut your text into perfectly organized new columns.
Step-by-Step: How to Split Text into Columns
Let’s walk through a real-world example. Imagine you have a column called “Customer Email” (e.g., akira@datawithoutcode.com), and your boss wants a report showing which companies (domains) your customers are coming from.
Step 1: Add the Cell Splitter Node
Go to your Node Repository on the bottom left of your KNIME workspace, search for Cell Splitter, and drag it onto your canvas. Connect it to the node that contains your messy data.
(Tip: If your data has invisible spaces around the text, you might want to clean it first using the String Manipulation node. You can learn how in my guide on cleaning messy strings in KNIME.)
Step 2: Choose Your Target Column
Double-click the Cell Splitter node to open its configuration window. At the very top, you will see a dropdown menu labeled “Select column to split”. Choose your “Customer Email” column.
Step 3: Define Your Delimiter (The Scissors)
Right below the column selection, you will see a box named “Enter delimiter”. This is where you tell KNIME where to make the cut.
- To split an email address, type the @ symbol.
- To split a full name (like “Akira Tanaka”), press the Spacebar once.
- To split a CSV-style list (like “Apple,Banana,Orange”), type a Comma (,).
Step 4: Choose the Output Format
At the bottom of the configuration window, you have a section called “Output”. You have three main choices:
- As new columns: This is the most common choice. KNIME will create “Customer Email_Arr[0]” (the username) and “Customer Email_Arr[1]” (the domain).
- As list: This keeps the data in one column but turns it into a collection (useful for advanced looping, but skip this for now).
- As set: Similar to a list, but automatically removes duplicate values.
Select “As new columns”, click OK, and press F8 to execute the node.
Review Your Automated Results
Right-click the executed Cell Splitter node and select “Split result table”. You will instantly see that your single email column has been perfectly chopped into two brand new columns!
You can now easily rename these new columns to “Username” and “Company Domain” using a standard Column Rename node.
Conclusion: Your Next Steps
By mastering the Cell Splitter node, you have officially automated the tedious Excel “Text to Columns” wizard. The best part? When next month’s messy CRM data arrives, you don’t have to click through any wizards—your KNIME workflow will split the text automatically.
Now that your text data is beautifully separated into its own columns, what should you do with it? Usually, the next step is to summarize that data to see the big picture. For example, counting how many customers came from each company domain.
Are you ready to leave Excel Pivot Tables behind? Join me in our next Data Prep & ETL tutorial where I break down Pivot tables in KNIME: GroupBy and Pivoting nodes explained!
