# How to use the TEXTAFTER function

The TEXTAFTER function extracts a string after a specific substring in a given value.

Formula in cell E3:

The TEXTAFTER function is available to Excel 365 users.

#### Table of Contents

## 1. TEXTAFTER Function Syntax

TEXTAFTER(*input_text*,*text_after*, [*n*], [*ignore_case*])

## 2. TEXTAFTER Function Arguments

input_text |
Required. The original string. |

text_after |
Required. The string to search for, text after this string is extracted. |

[n] |
Optional. The instance of text_after string, default is 1. |

[ignore_case] |
Optional. FALSE represents case sensitive search, default is TRUE. |

## 3. TEXTAFTER Function example

The formula in cell E4 extracts text from cell B4 after given string in cell C3.

Formula in cell E4:

### Explaining formula

#### Step 1 - TEXTAFTER function

TEXTAFTER(*input_text*,*text_after*, [*n*], [*ignore_case*])

#### Step 2 - Populate arguments

*input_text - B4
text_after - C4
[n] - Optional, default is 1.
[ignore_case] - Optional, default is TRUE meaning not case sensitive*

#### Step 3 - Evaluate function

TEXTAFTER(B4, C4)

becomes

TEXTAFTER("A dolphin is also breathing air.", "is")

and returns " also breathing air.".

## 4. TEXTAFTER Function - case sensitive example

This example shows how to use the TEXTAFTER function also considering upper and lower letters.

Formula in cell E4:

### Explaining formula

#### Step 1 - TEXTAFTER function

TEXTAFTER(*input_text*,*text_after*, [*n*], [*ignore_case*])

#### Step 2 - Populate arguments

*input_text - B4
text_after - C4
[n] - Optional, default is 1.
[ignore_case] - FALSE meaning case sensitive.*

#### Step 3 - Evaluate function

TEXTAFTER(B4, C4,,FALSE)

becomes

TEXTAFTER("A blue whale breathes Blue air.", "Blue", , FALSE)

and returns " air." in cell E3.

## 5. TEXTAFTER Function alternative (earlier Excel versions)

The TEXTAFTER 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 TEXTAFTER Function alternative

This formula performs the same thing as the TEXTAFTER function, it works in all Excel versions. The formula extracts text after a given substring.

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

#### Step 2 - Count characters in cell B3

The LEN function returns a number representing the number of characters for a given string.

LEN(*text*)

LEN(B3)

becomes

LEN("A blue whale breathes air.")

and returns 26.

#### Step 3 - Count characters in cell C3

LEN(*C3*)

becomes

LEN("breathes")

and returns 8.

#### Step 4 - Extract text after based on position

The RIGHT function extracts a specific number of characters always starting from the right of the text string.

RIGHT(*text*, [*num_chars*])

RIGHT(B3, LEN(B3)-SEARCH(C3, B3)-LEN(C3))

becomes

RIGHT("A blue whale breathes air.", 26-14-8+1)

becomes

RIGHT("A blue whale breathes air.", 5)

and returns " air.".

## 5.2 TEXTAFTER Function alternative - case sensitive

This formula performs a case-sensitive search in order to extract text after 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("a", "A blue whale breathes air.")+1

becomes

10+1 equals 11.

#### Step 2 - Count characters in cell B3

The LEN function returns a number representing the number of characters for a given string.

LEN(*text*)

LEN(B3)

becomes

LEN("A blue whale breathes air.")

and returns 26.

#### Step 3 - Count characters in cell C3

LEN(*C3*)

becomes

LEN("a")

and returns 1.

#### Step 4 - Extract text after based on character position

The RIGHT function extracts a specific number of characters always starting from the right of the text string.

RIGHT(*text*, [*num_chars*])

RIGHT(B3,LEN(B3)-FIND(C3,B3)-LEN(C3)+1)

becomes

RIGHT("A blue whale breathes air.",26-11-8+1)

becomes

RIGHT("A blue whale breathes air.",16)

and returns "le breathes air.".

## 6 Extract string between two substrings

The formula in cell F3 extracts a string between two given substrings, they are in cells D3 and D6. The original string is in cell B3.

Formula in cell F3:

=TEXTBEFORE(TEXTAFTER(B3, D3), D6)

### Explaining formula

#### Step 1 - Extract text after given string

TEXTAFTER(B3, D3)

becomes

TEXTAFTER("A blue whale breathes air.", "blue")

and returns " whale breathes air.".

#### Step 2 - Extract text before given string

The TEXTBEFORE function extracts a string before a specific substring from a given value.

TEXTBEFORE(*input_text*,*text_after*, [*n*], [*ignore_case*])

TEXTBEFORE(TEXTAFTER(B3, D3), D6)

becomes

TEXTBEFORE(" whale breathes air.", D6)

becomes

TEXTBEFORE(" whale breathes air.", "breathes")

and returns " whale ".

Use the TRIM function to remove leading and trailing spaces.

### Functions in 'Text' category

The TEXTAFTER 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