Excel TEXTBEFORE function
The TEXTBEFORE function returns the text that occurs before a given substring or delimiter.
Note: This function is only available in Excel for Microsoft 365 on the Insider channel.
Syntax
=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
Arguments
Remarks
Return value
It returns the extracted text string.
Example
Here, we take the following text list as an example to demonstrate how to use the TEXTBEFORE function to extract text under different conditions.
#Example1: Extract the text before the first delimiter in Excel
To extract the text that occurs before the first comma in the specified cells, please apply the TEXTBEFORE function as follows.
Select a cell next to the first text cell where you want to extract the text from, enter the following formula and press the Enter key to get the result. Select this result cell, drag its AutoFill Handle down to get the rest of the results.
=TEXTBEFORE(A2,",")
Note: In this case, only the first two arguments are required in the formula.
#Example2: Extract the text before the Nth delimiter in Excel
By default, the TEXTBEFORE function extracts the text before the first delimiter in the text string, even if there are multiple delimiters in the text. If you want to extract the text before the Nth delimiter, such as the second comma in this case, you can apply the TEXTBEFORE function as follows.
Select a cell next to the first text cell where you want to extract the text from, enter the following formula and press the Enter key to get the result. Select this result cell, drag its AutoFill Handle down to get the rest of the results.
=TEXTBEFORE(A2,",",2)
Note: To extract the text that occurs before the nth delimiter, you just need to specify the Instance_num argument as the corresponding number. In this case, I enter the number 2 to indicate the second comma.
#Example3: Extract the text before a given substring (case insensitive) in Excel
By default, the TEXTBEFORE function is case sensitive. If you want to extract text without case sensitivity, you can apply the TEXTBEFORE function as follows.
Select a cell next to the first text cell where you want to extract the text from, enter the following formula and press the Enter key to get the result. Select this result cell, drag its AutoFill Handle down to get the rest of the results.
=TEXTBEFORE(A2,"Consultant",1,1)
Note: To disable the case sensitivity in the TEXTBEFORE function, you need to specify the Match_mode argument as 1. In this case, the text that occurs before the substring “Consultant” or “CONSULTANT” will be extracted.
#Example4: Return a custom value when TEXTBEFORE does not match any text
As shown in the screenshot above, by default, the TEXTBEFORE function returns #N/A when no matches are found. To return a custom value when TEXTBEFORE does not match any text, you can apply the TEXTBEFORE function as follows.
Select a cell next to the first text cell where you want to extract the text from, enter the following formula and press the Enter key to get the result. Select this result cell, drag its AutoFill Handle down to get the rest of the results.
=TEXTBEFORE(A2,"Consultant",1,1,,"text not found")
Note: In this formula, “text not found” is the custom value to return if there are no matched texts found by the TEXTBEFORE function. You can change it as you need.
#Example5: Extract the text before the last delimiter in Excel
Usually, you can specify the instance_num parameter to extract the text before the nth delimiter in a text string. If you need to extract the text before the last delimiter, the easiest way to accomplish this task is to specify a negative number for this parameter.
For example, to extract the text that occurs before the last comma in the table below, the TEXTBEFORE function can be changed as follows.
Select a cell (say B2 in this case), enter the following formula and press the Enter key. Select this result cell and drag its AutoFill Handle down to get the rest of the results.
=TEXTBEFORE(A2,",",-1)
Note: Here -1 in the formula helps to search the original text string from the end.
Related Functions
Excel TEXTAFTER function
The TEXTAFTER function extracts and returns the text that occurs after a given substring or delimiter.
Excel TEXTSPLIT Function
The TEXTSPLIT function splits text strings by a given delimiter to an array across columns and rows.
Excel VALUETOTEXT Function
The VALUETOTEXT function converts any specified value to text.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
