The Excel TEXTBEFORE and TEXTAFTER functions make it easy to extract text from a cell.
Use the TEXTBEFORE function in Excel to extract characters before a given delimiter, and the Excel TEXTAFTER function to extract characters after a given delimiter.
Let’s look at examples of these excellent Excel functions in action.
Download the sample workbook to follow along.
Excel TEXTBEFORE and TEXTAFTER Function Syntax
The syntax for both functions is the same (they just have different names and missions).
=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])- text: The text that you need to extract characters from.
- delimiter: The character to use as the delimiter. For TEXTBEFORE, this character signals the end of the characters to extract. And for TEXTAFTER, it signals the point after which characters should be extracted.
- [instance_num]: The instance number of the delimiter character. This is an optional argument. If omitted, the first instance of the delimiter is used.
- [match_mode]: Specify if you need a case-sensitive match or not. The default is case-sensitive.
- [match_end]: If the delimiter is not found, should the end of the text string be used as the match. By default, this is not used. There is an example of the use of this argument in the tutorial.
- [if_not_found]: An action to perform if there is no match to the delimiter. If neither this argument or the match end are used, the #N/A error is returned.
Excel TEXTBEFORE Function Example
The following formula uses the TEXTBEFORE function to extract the characters before the forward slash “/” delimiter.
=TEXTBEFORE(A2,"/")
The TEXTBEFORE function has made this task super simple. Only the text and delimiter needed to be provided.
Before the introduction of TEXTBEFORE, we used the LEFT function to extract the text, and the FIND or SEARCH functions to locate the position of the delimiter. Fortunately, we no longer need to perform these formula gymnastics.
=LEFT(A2,FIND("/",A2)-1)Excel TEXTBEFORE and TEXTAFTER Together
In this next example, the Excel TEXTBEFORE and TEXTAFTER functions are used together to extract the characters between two delimiters i.e., two forward slashes “/”.
The TEXTAFTER function is used to extract the characters after the first “/” delimiter producing a result of “THG/4” from the value in cell A2. The TEXTBEFORE function then extracts the characters before the “/” delimiter producing “THG”.
=TEXTBEFORE(TEXTAFTER(A2,"/"),"/")
TEXTBEFORE a Specific Instance of the Delimiter
For the next example, we have the following list of URLs.
We will extract the root domain from each URL in the list. This requires us to extract all the characters before the third occurrence of the “/” delimiter.
We have a problem, in that the fourth URL “https://computergaga.com” does not contain a third occurrence of the “/” delimiter.
The following TEXTBEFORE function is used in the table and successfully returns all domains except the problematic fourth URL, which returns the #N/A error. We enter 3 for the instance num argument.
=TEXTBEFORE([@URLs],"/",3)
Using Match End with TEXTBEFORE
Let’s edit this formula to take advantage of the match end argument in the Excel TEXTBEFORE function.
We ignore the match mode argument (the fourth argument) in the TEXTBEFORE function and enter 1 to enable the match end argument. When the delimiter is not found, the end of the text string is used instead.
=TEXTBEFORE([@URLs],"/",3,,1)Now, we return the domain for the fourth URL as well.
The If Not Found Argument with TEXTBEFORE
An alternative way of suppressing the #N/A error is to use the if not found argument of the TEXTBEFORE and TEXTAFTER functions. This is similar to how you may have used the IFERROR or IFNA functions before.
We use if not found to provide an alternative action to returning the text string when the delimiter is not found. For example, we may want to return an empty string instead.
In the following formula, the match end argument has been ignored (note the three commas after the instance num argument), and an empty string “” has been specified for the if not found argument.
=TEXTBEFORE([@URLs],"/",3,,,"")
The use of the match end argument made more sense in this example, as our goal was to return the root domains. However, it is important to see other options, and with the if not found argument we could have provided any value or alternative formula.
TEXTAFTER Function to Extract Sheet Name from File Path
Let’s now use the Excel TEXTAFTER function to return the sheet name of the active sheet.
To do this, we will use the CELL function in Excel to return the file path of this workbook, and then TEXTAFTER to return the text after the closing square bracket “]”.
The CELL function returns information about a cell such as formatting, location or its locked status. We use the following formula to return the file path of the workbook in which the cell resides.
=CELL("filename")
The sheet name is at the end of this text string and follows the closing square bracket. So, to extract the sheet name only, we need all the text after the closing square bracket “]”.
We enter the following formula in cell B3 to successfully extract the sheet name.
=TEXTAFTER(CELL("filename"),"]")
Instance of Delimiter from End of a String
In the final example of this Excel tutorial, we see how to specify an instance number of a delimiter from the end of a text string.
We want to extract the name of the folder that the workbook is saved from the file path. To do this, we need to extract the characters between the 2nd and 1st instance of a slash “/” from the end of the string.
In the TEXTAFTER function, -2 is used from the instance num argument. It is that beautifully simple to specify an instance number from the end of a string.
=TEXTAFTER(CELL("filename"),"/",-2)This returns all text after the 2nd slash “/” from the end of the string.
We use the TEXTBEFORE function to extract all text before the slash “/” delimiter and get the folder name.
=TEXTBEFORE(TEXTAFTER(CELL("filename"),"/",-2),"/")
The Excel TEXTBEFORE and TEXTAFTER functions make the task of extracting characters from strings easier than it has been in the past.
These examples have hopefully demonstrated how brilliant they are. They are just a couple of the 150+ Excel functions demonstrated in my Advanced Excel Formulas book.
