In Excel, you can split text on a delimiter without a formula; the Text to Columns tool is perfect.
However, new features like Power Query or the TEXTSPLIT function offer even better options for splitting text on a delimiter.
Step-by-Step Guide to Split Text on a Delimiter
Using Text to Columns to split text on a delimiter with no formula keeps your data clean without complex functions.
- Select the cells containing the text you want to split.
- Click the Data tab in the Ribbon and choose Text to Columns.
- At step 1: Select Delimited and click Next.
- Step 2: Choose your delimiter. If your delimiter is not in the list, you select Other and you write your delimiter. The preview shows how your text will split.
- Click Next to set the destination of your split data.
- Press Finish to complete.
It is ideal for importing CSV files with a few clicks. However, 2 new modern features provide a better solution for splitting text on a delimiter.
Power Query: A Modern Option to Split Your Text
But, the Text to Columns tool is limited 🫤
Nowadays, Power Query is the dedicated tool to split text on a delimiter, and it offers more options:
- Split Text Easily with Multiple Delimiters: Power Query lets you split text with one or more delimiters, simplifying complex data handling.
- Replay the Import Process: You can save and replay the steps any time. Power Query applies the changes automatically when your data updates.
- Add Extra Steps for Cleaning: Easily remove rows, filter data, or adjust values during the import process, all in one place.
- And all of that, without writing a single line code 😉
Power Query offers more control and flexibility than the traditional Text to Columns tool, making data transformation easier.
TEXTSPLIT Function: A Powerful Formula
The new TEXTSPLIT function of Excel 365 splits text straightforwardly. You just have to
- Indicate the cell with your text to split
- Indicate the delimiter
And that’s all. Automatically, the function will split your text on your delimiter.
Conclusion
While the classic Text to Columns tool still works for simple tasks, Power Query and TEXTSPLIT provide efficient, modern solutions for complex data handling.
