How to use the TEXTBEFORE function
The TEXTBEFORE function extracts a string before a specific substring from a given value.
Formula in cell E3:
The TEXTBEFORE function is available to Excel 365 users.
Table of Contents
1. TEXTBEFORE Function Syntax
TEXTBEFORE(input_text,text_before, [n], [ignore_case])
2. TEXTBEFORE Function Arguments
| input_text | Required. The original string. |
| text_before | Required. The string to search for, text before this string is extracted. |
| [n] | Optional. The instance of text_before string, default is 1. |
| [ignore_case] | Optional. FALSE represents case sensitive search, default is TRUE. |
3. TEXTBEFORE Function example
Formula in cell E4:
Explaining formula
Step 1 - TEXTBEFORE function
TEXTBEFORE(input_text,text_before, [n], [ignore_case])
Step 2 - Populate arguments
input_text - B4
text_before - C4
[n] - Optional, default is 1.
[ignore_case] - Optional, default is TRUE meaning not case sensitive
Step 3 - Evaluate function
TEXTBEFORE(B4, C4)
becomes
TEXTBEFORE("A dolphin is also breathing air.", "is")
and returns "A dolphin".
4. TEXTBEFORE Function - case sensitive example
This example shows how to use the TEXTBEFORE function also considering upper and lower letters.
Formula in cell E4:
Explaining formula
Step 1 - TEXTBEFORE function
TEXTBEFORE(input_text,text_before, [n], [ignore_case])
Step 2 - Populate arguments
input_text - B4
text_before - C4
[n] - Optional, default is 1.
[ignore_case] - FALSE meaning case sensitive.
Step 3 - Evaluate function
TEXTBEFORE(B4, C4,,FALSE)
becomes
TEXTBEFORE("A blue whale breathes air.", "a", , FALSE)
and returns "A blue wh".
5. TEXTBEFORE Function alternative (earlier Excel versions)
The TEXTBEFORE function lets you perform both a regular and a case-sensitive search based on the fourth argument [ignore_case]. The SEARCH and FINDÂ functions are used in the alternative formulas below.
5.1 TEXTBEFORE Function alternative
This formula works in all Excel versions.
Formula in cell E4:
Explaining formula
Step 1 - Find character position of substring
The SEARCH function returns a number representing the position of character at which a specific text string is found reading left to right. It is NOT a case-sensitive search.
SEARCH(find_text,within_text, [start_num])
SEARCH(C3, B3)-1
becomes
SEARCH("breathes", "A blue whale breathes air.")-1
becomes
14-1 equals 13.
Step 2 - Extract text before based on position
The LEFT function extracts a specific number of characters always starting from the left.
LEFT(text, [num_chars])
LEFT(B3, SEARCH(C3, B3)-1)
becomes
LEFT("A blue whale breathes air.", 13)
and returns "A blue whale ".
5.2 TEXTBEFORE Function alternative - case sensitive
This formula performs a case-sensitive search in order to extract text before the given substring.
Formula in cell E3:
Explaining formula
Step 1 - Find character position of substring
The FIND function returns a number representing the position of character at which a specific text string is found reading left to right. This function performs a case-sensitive search.
FIND(find_text,within_text, [start_num])
FIND(C3, B3)-1
becomes
FIND("breathes", "A blue whale breathes air.")-1
becomes
10-1 equals 9.
Step 2 - Extract text before based on position
The LEFT function extracts a specific number of characters always starting from the left.
LEFT(text, [num_chars])
LEFT(B3, FIND(C3, B3)-1)
becomes
LEFT(B3, 9)
becomes
LEFT("A blue whale breathes air.", 9)
and returns "A blue wh".
Useful links
TEXTBEFORE function - Microsoft
Excel TEXTBEFORE function - extract text before character (delimiter)
Functions in 'Text' category
The TEXTBEFORE function function is one of many functions in the 'Text' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form