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".
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