Excel - Using TextBefore and TextAfter Functions

Posted on:  04/01/2024

Introduction

In Microsoft 365, there are several handy functions that can make data manipulation and analysis much easier. Today, I want to focus on two of my favorite functions - TEXTBEFORE and TEXTAFTER. These text functions are exclusive to Microsoft 365 and can help you extract specific parts of a text string. In this blog, I will provide a quick review of each function and then show you how to combine them for even more powerful results.

Text Before Function

Let's start with the TextBefore function. This function allows you to extract the text that appears before a specified delimiter in a given text string. To demonstrate, let's look at an example. In column A, I have a list of states, county names, and cities. Our goal is to extract just the city names. To achieve this, I'll use the Text Before function. In cell B2, I'll enter the formula "=TEXTBEFORE(A2, "-")". Here, "A2" represents the text string we want to extract from, and "-" is the delimiter we want to use. By using the TextBefore function, I can extract the text before the first hyphen in cell A2. For example, in cell B2, the formula will return "GA" for Georgia. If I modify the formula to "=TEXTBEFORE(A2, "-", 2)", I can extract the text before the second hyphen, which in this case is "GA-Cobb". This way, you can easily extract specific parts of a text string based on your desired delimiter.

TEXTBEFORE Syntax

=TEXTBEFORE(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])

The TEXTBEFORE function syntax has the following arguments:

text       The text you are searching within. Wildcard characters are not allowed. If text is an empty string, Excel returns empty text. Required.

delimiter       The text that marks the point before which you want to extract. Required.

instance_num       The instance of the delimiter after which you want to extract the text.   By default, instance_num = 1.  A negative number starts searching text from the end. Optional.

match_mode    Determines whether the text search is case-sensitive. The default is case-sensitive. Optional. Enter one of the following:

0      Case sensitive.

1      Case insensitive.

match_end     Treats the end of text as a delimiter. By default, the text is an exact match. Optional. Enter the following:

0      Don't match the delimiter against the end of the text.

1      Match the delimiter against the end of the text.

if_not_found    Value returned if no match is found. By default, #N/A is returned. Optional.

YouTube Video

Text After Function

Now, let's explore the opposite function - TextAfter. This function allows you to extract the text that appears after a specified delimiter in a given text string. Similar to the Text Before function, we'll use a delimiter to specify where the extraction should start. To demonstrate, let's continue with our example. In cell C2, I'll enter the formula "=TEXTAFTER(A2, "-")". This formula will extract the text after the hyphen in cell A2. By dragging the formula down, we can extract the city names for the entire list. For example, in cell C2, the formula will return "Cobb-Marietta". By using the Autofill feature, we can easily apply the formula to all the cells in column C, extracting the city names for the entire list.

TEXTAFTER Syntax

=TEXTAFTER(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])

The TEXTAFTER function syntax has the following arguments:

text       The text you are searching within. Wildcard characters not allowed. Required.

delimiter       The text that marks the point after which you want to extract. Required.

instance_num       The instance of the delimiter after which you want to extract the text.  By default, instance_num = 1.  A negative number starts searching text from the end. Optional.

match_mode    Determines whether the text search is case-sensitive. The default is case-sensitive. Optional. Enter one of the following:

0      Case sensitive.

1      Case insensitive.

match_end    Treats the end of text as a delimiter. By default, the text is an exact match. Optional. Enter one of the following:

0      Don't match the delimiter against the end of the text.

1      Match the delimiter against the end of the text.

if_not_found    Value returned if no match is found. By default, #N/A is returned. Optional.

Combining TextBefore and TextAfter

Now that we understand how the Text Before and Text After functions work individually, let's explore their combined power. By combining these functions, we can extract the text between two delimiters. To do this, we will start with the Text After function. In cell D2, I'll enter the formula "=TEXTAFTER(A2, "-")". This will give us the same result as in cell C2. Next, I'll edit the formula in cell D2 to include the TextBefore function. I'll modify the formula to "=TEXTBEFORE(TEXTAFTER(A2,"-"),"-")". By doing this, we extract just the city name without the county or state information. For example, in cell D2, the formula will return "Cobb". By dragging the formula down, we can extract the city names for the entire list. This combination of functions is not only useful for our example but can also be applied to various scenarios, such as extracting specific numbers from product codes or accounting data.

Recent Articles

Categories

• Blog » Microsoft » Microsoft Excel » Advanced