# 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 this article

### 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 signsUse 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 OscarYou can contact me through this contact form