In Excel, you can split text on a delimiter without a formula; the Text to Columns tool is perfect for this. 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.

1. Select the cells containing the text you want to split.
2. Click the Data tab in the Ribbon and choose Text to Columns.

3. At step 1: Select Delimited and click Next.
4. 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.

3. Click Next to set the destination of your split data.
4. 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

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, making complex data handling simple.
• 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 as part of 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.