The TEXTBEFORE() and TEXTAFTER() functions in Excel represent a significant enhancement to the traditional Text To Columns feature, offering a higher degree of customization and auditability. These functions allow users to extract substrings from a cell based on specified delimiters or occurrences directly within formulas, providing a dynamic and transparent method that adjusts automatically as data changes. This formula-driven approach not only simplifies the extraction of text segments without altering the original data structure.
This post will look at how to use TEXTBEFORE() and TEXTAFTER() to extract text from a cell appearing before and after a specified delimiter, respectively. You can follow along by downloading the exercise file below:
Let’s quickly review the parameters of these two functions by placing them side by side. This comparison highlights just how similar they are! One function specifically searches for text before a delimiter, while the other looks after it:
| Argument | TEXTBEFORE Description | TEXTAFTER Description |
|---|---|---|
| text | The text from which to extract characters. | The text from which to extract characters. |
| delimiter | The delimiter where the extraction ends. Extracts everything before this delimiter. | The delimiter where the extraction begins. Extracts everything after this delimiter. |
| instance_num | Optional. Specifies which occurrence of the delimiter to look for. The default is the first occurrence. | Optional. Specifies which occurrence of the delimiter to look for. The default is the first occurrence. |
| if_not_found | Optional. Specifies a return value if the delimiter is not found. If omitted, the function returns an error. | Optional. Specifies a return value if the delimiter is not found. If omitted, the function returns an error. |
Because of their similarities, we’ll examine these functions together for these examples. Let’s start with something basic. I’ll search for a colon in cell A2. TEXTBEFORE() will extract the text before the colon, and TEXTAFTER() will grab the text after it. No big surprises there.
=TEXTBEFORE(A2, ":")
=TEXTAFTER(A2, ":")
Keep in mind that if you wanted to achieve this in one go, proverbially killing two birds with one stone, you could use the TEXTSPLIT() function to split the results into a dynamic array with just one function:
Great work! By default, both of these functions will split at the first occurrence of the specified delimiter. If you want to change that to another instance, you can set the instance number to something else, for example, 2. The same principle applies here: one function retrieves the text before the specified instance, and the other gets the text after it.
=TEXTBEFORE(A2, ":", 2)
=TEXTAFTER(A2, ":", 2)
Just like other Excel functions, if no match is found with these functions, you’ll receive an #N/A error. To override this, let’s use the last argument. If there’s nothing to delimit before or after, we can customize the output to something like "Match not found".
=TEXTBEFORE(A2, "_", , , , "Match not found")
=TEXTAFTER(A2, "_", , , , "Match not found")
Last but not least, let’s examine an example using multiple criteria and case sensitivity. In this scenario, we’re looking for “and’s” or “or’s”, but our data includes a mix of proper, upper, and lowercase letters. Therefore, we’ll specify that we want this search to be case-insensitive. If splitting by either “and” or “or” works for our situation, you can enclose both delimiter options within curly braces:
=TEXTBEFORE(A2, {"and","or"}, , 1, , )
=TEXTAFTER(A2, {"and","or"}, 2, 1, , )
Conclusion
In conclusion, the addition of TEXTBEFORE() and TEXTAFTER() to Excel’s functions marks a major advancement in both functionality and clarity when compared to older techniques such as Text to Columns or manually combining legacy text functions like LEFT() and MID(). These new functions enable more straightforward and intuitive text handling right within your formulas. I encourage you to explore these capabilities, share your innovative applications, experiences, or any questions you might have in the comments below.
Leave a Reply