TEXTAFTER with TEXTBEFORE

So how can I get the color only?

The trick is to use the TEXTAFTER and TEXTBEFORE functions together.

First, I’ll use the TEXTAFTER function to remove the 2-letter code.

Next, I’ll add the TEXTBEFORE function.

Since TEXTAFTER has already removed the 2-letter code, only the color and number remain.

The result is the color only.

Let’s look at a new example.

In this worksheet, we have location data in column B.

This gives me the state name and the zip code.

To remove the zip code, I can use TEXTBEFORE.

The text comes from TEXTAFTER.

The delimiter is a single space.

An easy fix is to provide a negative one for instance number.

This causes TEXTBEFORE to count backwards and use the last space.

As you’re able to see, this works correctly.

You might wonder if it matters if I start with TEXTBEFORE or TEXTAFTER?

In this problem, no.

Then I can use TEXTAFTER like I did originally, with a comma and space for the delimiter.

The result is exactly the same.