Extracting words from a sentence

In the “Excel Kitchenette” blog so far you could have met many text manipulation functions. As of August 2022 (unofficially), and officially as of a few weeks ago, Microsoft 365 subscribers have some new functions at their disposal that increase productivity and can help you more easily to retrieve individual parts of a given text, before or after the delimiter.

The TEXTBEFORE function allows you to extract a part from the given text before delimiter. Its syntax:

TEXTBEFORE (<text>, <delimiter>, [<instance number>], [<mode>],
[<end>], [<no result>])

The simplest way to use this function is to specify a text and a delimiter – the character used for separation. If we give the sentence “An apple a day keeps the doctor away.” and write the formula:

=TEXTBEFORE(A1,” “)

As a result we will get the first word before the delimiter, i.e. “An”.

If we add the number of instances, e.g. 4, using the following formula:

=TEXTBEFORE(A1,” “;4)

the result will be the first three words, i.e. “An apple a day”. Mode argument indicates whether the search is case sensitive. The end is an argument with which we can set the separation of words to start from the end, and the last argument of the function serves to define the value that appears if there are no results.

The TEXTAFTER function is used to search for text after a specified delimiter. Its syntax:

TEXTAFTER (<text>, <delimiter>, [<instance number>], [<mode>],
[<end>], [<no result>])

Similar to the previous example, if we enter the formula:

=TEXTAFTER(A1,” “)

the result is the sentence “apple a day keeps the doctor away.”. If we set 4 as the number of instances, with a slightly more complex formula:

=TEXTAFTER(A1,” “,4,0,0,”No results”)

the result is a “keeps the doctor away”.

As you can see, these two functions are very useful if you need to extract one or more words from a sentence.