How to Split Text to Columns Automatically Using Formulas in Excel

Excel’s Text to Columns tool is useful when separating values like codes, names, or product IDs into multiple parts. However, it’s not dynamic and you must reapply it every time your data changes. If you want a smarter way to split text into columns automatically, formulas in Excel offer a smart solution.

In this article, we’ll walk through several practical methods to split text using formulas, all of which update automatically when your source data changes. Let’s get started.

When your data follows a consistent format like codes that always contain the same number of characters in each section, the LEFT, MID, and RIGHT functions are the most reliable tools for breaking it apart. This method is especially useful for parsing structured identifiers like product codes, region tags, or user IDs without relying on any delimiters. It’s fully compatible with older Excel versions and doesn’t require advanced formulas.

We’ll use a sample dataset containing 10 rows of product codes, each composed of a 2-letter country prefix, a 3-digit region number, and a 3-digit product ID. These values are embedded into a single cell, and we’ll extract them cleanly into separate columns using simple text functions.

Steps:

➤ In cell C2, extract the Country Code by entering the following formula:

➤ In cell D2, extract the Region Code by entering the following formula:

➤ In cell E2, extract the Product ID by entering the following formula:

➤ Drag all three formulas down to row 11 to cover the full dataset.

Now your structured codes are split cleanly into separate columns.

When your data contains values separated by consistent delimiters such as commas, dashes, or pipes, then you can use a combination of FILTERXML and SUBSTITUTE functions to automatically split each part into separate columns. This method works particularly well for dynamic data where the number of segments is fixed and each entry follows the same delimiter-based structure. It’s a smart workaround that mimics the Text to Columns feature using a formula, though it’s only available in Excel for Windows.

We’ll use a dataset with SKU-style codes in column B, where each value includes a country code, a numeric region ID, and a product group number separated by commas (e.g., US,010,23). Using the FILTERXML function, we’ll split these into separate columns for cleaner analysis.

Steps:

➤ In cell C2, enter the following formula:

➤ Press Enter and the formula will spill from C2:E2.

➤ Drag the formula down to row 11 to cover your dataset.

Each value before or after a comma is extracted into a new cell. Just make sure your data includes a consistent delimiter like commas.

If you’re working in Excel 365 or Excel 2021, the TEXTSPLIT function offers one of the easiest ways to break apart text based on any consistent delimiter such as dashes, commas, or pipes. This modern function is dynamic, clean, and doesn’t require helper columns or nested formulas. As long as each entry follows a consistent structure, TEXTSPLIT will automatically place each piece into its own column, updating in real time if the source cell changes.

Steps:

➤ In cell C2, use the formula:

Here, you can change comma delimiter to any other delimiter you might have in your dataset.

➤ Hit Enter, and the result will spill across C2:E2.

➤ Drag down to apply it to other rows.

Now the TEXTSPLIT function auto-adjusts and separates each part into its column based on the delimiter you choose.

If you’re using Excel 2010, 2013, or any version without dynamic array support, you can still split comma-separated values into individual columns using a formula based on MID, SUBSTITUTE, and COLUMNS. This technique works by padding each delimiter with spaces and slicing the text according to position without requiring modern functions like TEXTSPLIT or FILTERXML. It’s a great solution when working in older Excel environments.

Steps:

➤ In cell C2, use this formula to get the first part:

➤ Press Enter for output.
➤ Drag across to columns D2 and E2.

➤ Then drag all three columns down to row 11.

Each dragged formula will return the next comma-separated part of the text dynamically.

Frequently Asked Questions

Can I use formulas to split structured codes without commas?

Yes. If your codes follow a fixed pattern like two letters followed by digits, then you can reliably split them using LEFT, MID, and RIGHT functions. These formulas work well without needing any delimiter.

What if my data uses dashes or pipes instead of commas?

No problem at all. You can replace the delimiter in your formulas with any character, such as a dash (–), pipe (|), or semicolon. Functions like TEXTSPLIT and SUBSTITUTE are fully customizable for this.

Which formula works in Excel 2010 or older versions?

For older versions of Excel, use the LEFT, MID, RIGHT method or the MID, SUBSTITUTE and COLUMNS technique. Both work reliably without requiring dynamic arrays or newer Excel functions like TEXTSPLIT.

Can these formulas update automatically?

Yes, that’s the best part. All four formula-based methods dynamically respond to changes. When you update the original text cell, the split values automatically refresh without needing to reapply the formula or tool.

Is FILTERXML available on Excel for Mac?

No. The FILTERXML function is exclusive to Windows versions of Excel. If you’re on Mac, consider using TEXTSPLIT (if supported) or go with classic methods like MID and SUBSTITUTE.

Wrapping Up

In this tutorial, we explored multiple ways to split text into columns automatically in Excel using formulas. We learned how to use functions like LEFT, MID, and RIGHT for fixed patterns, and more flexible options like FILTERXML, TEXTSPLIT, and SUBSTITUTE-based methods for delimiter-based text. Feel free to download the practice file and share your feedback.