Excel TEXTAFTER function

Abstract

In this video, we’ll take a look at the TEXTAFTER function.

Let’s look at an example.

In this worksheet, we have a list of email addresses.

TEXTAFTER accepts six arguments, but only the first two arguments,textanddelimiter, are required.

In this case, the text comes from column B, and the delimiter is the @ symbol.

Notice I need to wrap the “@” symbol in double quotes to indicate a text value.

When I enter the formula, I get just the domain.

Now I can copy the formula down to extract the domain from all emails.

Let’s look at a new example.

The goal is to extract just the zip code.

For these locations, a single space is the delimiter.

However, the text contains more than one space.

By default, TEXTAFTER will use the first space it finds.

How can we tell TEXTAFTER to use the second space?

To get text after thesecondspace, I need to provide the number 2.

What we really need here is a way to target thelastspace.

Luckily, TEXTAFTER supports negative instance numbers.

Now the formula returns the zip code in all cases.

For the last example, let’s look at how TEXTAFTER handles upper and lower case.

Also notice that the “x” in rows 8, 9, and 10 is uppercase.

How will TEXTAFTER handle this?

Let’s find out.

This tells us that TEXTAFTER is case-sensitive by default.

This tells TEXTAFTER to perform a case-insensitive match.

Once I do that, the formula works as expected.

One simple way to force a numeric result is to add zero.

The math operation causes Excel to perform this conversion automatically.