Cat On A Spreadsheet

One of the realities of working with data is that very little of it arrives in the format you would actually like to use. Export a report from an accounting package, retrieve a customer list from a CRM system, or receive a file from a third-party supplier, and sooner or later you will encounter the same familiar problem: several pieces of information have been compressed into a single cell, separated by commas, semicolons, pipes, or some other delimiter.

For years, these datasets represented one of the strongest arguments for writing VBA. A few loops, some string manipulation, and the problem was solved. Alternatively, if the transformation only needed to happen once, the Text to Columns wizard often provided a quick, if rather static, solution.

Modern Excel offers a different approach. With functions such as TEXTSPLIT, TEXTBEFORE, and TEXTAFTER, combined with dynamic arrays, it is now possible to build parsing formulas that are entirely dynamic. More importantly, these formulas can adapt automatically as the source data changes, eliminating the need for repeated imports or manual processing.

Perhaps the greatest advantage, however, is that these functions encourage us to think less about characters and positions, and more about the structure of the information itself.

Looking Beyond the First Delimiter

The simplest text extraction tasks usually involve retrieving everything before or after the first delimiter.

Suppose a cell contains:

Extracting the first name is now almost trivial:

Likewise, retrieving everything after the first comma is equally straightforward:

These functions are already significantly more readable than the combinations of LEFT, RIGHT, MID, FIND, and LEN that they replace. Yet the real power of the modern text functions becomes apparent when we move beyond the first delimiter.

The Often Overlooked Instance Number

Many Excel users discover TEXTAFTER and assume it can only work with the first occurrence of a delimiter. In reality, both TEXTAFTER and TEXTBEFORE include an optional instance number parameter that allows you to target any occurrence of the delimiter.

Consider the same sample text:

If we ask for the text after the second comma:

the result is:

Likewise, asking for the text after the third comma returns:

This simple parameter often removes the need for nested formulas altogether.

Extracting the Second or Third Element

A slightly more interesting problem is retrieving a specific element from within a delimited string. Imagine that we want only the city, which is the third element.

One elegant solution combines TEXTAFTER and TEXTBEFORE:

Reading the formula almost feels like reading plain English: first, take everything after the second comma; then, from that result, take everything before the next comma.

The outcome is:

Notice how little attention is paid to character positions. We are describing the logical structure of the text rather than counting offsets.

Building the Pattern

Once this approach becomes familiar, extracting any element follows exactly the same pattern.

The second element:

The third element:

The fourth element:

Although the formulas appear slightly repetitive, the underlying logic remains extremely consistent: move to the required delimiter, take everything after it, then isolate everything before the next delimiter. Once understood, this technique becomes remarkably easy to apply.

When the Last Element Is Different

The final value presents an interesting variation. Suppose we need to retrieve the country:

There is no subsequent comma to stop at. Fortunately, this actually simplifies the formula:

Because nothing follows the third comma except the final element, no accompanying TEXTBEFORE call is necessary. This distinction is worth remembering because it often makes formulas shorter than expected.

Thinking Structurally Instead of Positionally

Perhaps the greatest benefit of these functions is not their brevity but the mindset they encourage. Older Excel formulas required us to think in terms of character positions.

Where is the sixth character?

How many characters exist before the second comma?

How many should be extracted?

Modern formulas ask much more meaningful questions.

What comes after the second delimiter?

What comes before the next delimiter?

These questions describe the structure of the data itself rather than its physical representation. As datasets become larger and more varied, this structural approach proves considerably easier to maintain.

Combining with Dynamic Arrays

Everything discussed so far becomes even more powerful when dynamic arrays are introduced.

Suppose column A contains several thousand customer records in the same format. Instead of filling formulas down manually, the transformation can be applied to the entire dataset in one operation:

The result spills automatically, producing the city for every record in the range. No copying, no helper columns, no VBA. The formula simply expresses the transformation, leaving Excel to perform the repetition automatically.

Making the Formula Reusable

By now, a pattern should be emerging. The only thing changing is the instance number. This naturally suggests that the logic itself could become reusable. Using LAMBDA, we might define a custom function such as:

After assigning it a meaningful name through the Name Manager, the workbook gains a reusable function capable of returning any element from a delimited string simply by specifying its position.

What once required VBA can now exist entirely within the worksheet.

The Bigger Picture

Extracting individual elements from delimited text may seem like a relatively modest task, but it illustrates something much larger. Modern Excel increasingly encourages us to describe transformations rather than procedures. We no longer explain how to move through a string character by character. Instead, we describe the relationships between pieces of information, allowing the formula engine to perform the mechanics on our behalf. That shift, repeated across hundreds of small improvements, is gradually changing the way sophisticated workbooks are designed. And yet, one practical challenge remains. Real-world data rarely contains exactly the number of delimiters we expect. Sometimes we want only the first few fields, leaving everything else untouched.

Handling that scenario elegantly will be the subject of our next article.

Full Service Consulting

Reporting

Automation

Cat On A Spreadsheet