Note: The other languages of the website are Google-translated. Back to English

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

Text (required): The original text string you want to extract text from. It can be supplied as:
-- A text string;
Or
-- A cell reference
Delimiter (required): A substring or delimiter that you want to extract the text before it;
Instance_num (optional): The instance of the delimiter in text;
-- The default is 1, which means that when there are duplicate delimiters appear in the text string, the text before the first delimiter will be extracted.
-- If you want to search for text starting from the end of the text string, specify a negative number for this parament.
Match_mode (optional): Determines whether the delimiter is case-sensitive;
-- 0 (default): Case sensitive.
-- 1: Case insensitive.
Match_end (optional): Treats the end of text as a delimiter. Default is 0.
-- 0 (default): Don’t match the delimiter against the end of the text.
-- 1: Match the delimiter against the end of the text.
If_not_found (optional): The value to return when no match is found. Default is #N/A error value.

Remarks

1) It is the opposite of the TEXTAFTER function.
2) When the delimiter is empty, you will get either of the following result:
-- When searching from the left side of the text string (instance_num is positive), the entire text string is returned;
-- When searching from the right side of the text string (instance_num is negative), the empty text is returned;
3) The #VALUE! error occurs if either of the following conditions is meet:
-- Instance_num = 0;
-- Instnce_num is greater than the length of text;
4) The #N/A error occurs if either of the following conditions is meet:
-- Delimiter does not exist in text string;
-- Instance_num is greater than the number of occurrences of delimiter in text string.

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

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.
Ribbon of Excel (with Kutools for Excel installed)

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, Firefox, And New Internet Explorer.
Screen Shot of Excel (with Office Tab installed)
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations

Follow Us

Copyright © 2009 - www.extendoffice.com. | All rights reserved. Powered by ExtendOffice. | Sitemap
Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.
Protected by Sectigo SSL