Back to Blog
Blog post image

How to use Excel's TEXTAFTER Function

Published
Sep 27, 2022 3:38:00 PM

Returns text from a cell that occurs after a given delimiter

What does Excel's TEXTAFTER function do?

The TEXTAFTERfunction is one of Excel's text functions.

TEXTAFTER returns the text from a text string that occurs after a given delimiter (character or string).

Here's a simple example

Let's take a look at a simple example of the TEXTAFTER function.

We would like to return the text that comes after the first space.

TEXTAFTER

=TEXTAFTER(A1," ")

What does that mean in plain English?

Return the text that comes after the first space.

How do I write a formula using the TEXTAFTER function?

=TEXTAFTER(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])

 

text – The text that you want to extract text from

delimiter – The character that you want to extract text after

[instance_num] – The instance of the delimiter you want to extract after. Defaul is 1. Use a negative number to start from the end.

[match_mode]– Case sensitive delimiter. TRUE = yes (default) , FALSE = no

[match_end]– Treat the end of the text string as the delimiter.

[if_not_found] – What to return if the delimiter provided is not found. By default, #N/A is returned

What to consider when using the TEXTAFTER function in your  model

  • The TEXTAFTER function was introduced in 2019, so be aware of compatibility issues for users of previous Excel versions.
  • Use a negative number in the instance_num arguement to start from the end.

 

Read more about the TEXTAFTER function on the Microsoft support page here.

Take our free 30-question skills assessment

See where you stand with your Excel, financial modelling, data visualisation & analytics, and modelling tech skills

Take Free Skills Assessment