Excel TEXTBEFORE function ( 2 examples)

Excel TEXTBEFORE Function

On March 16, 2022, Microsoft announced 14 new Excel functions. Most of them are text functions. The new Excel TEXTBEFORE function is one the most useful function. It is high time to learn about this function.

Examples:

• How to generate text before first space?
• How to generate text before second space?

---

Summary

The is function returns a string of text that occurs before a given substring in that string. It is exactly opposite of TEXTAFTER. This function is more similar to the LEFT function; however, this function is more flexible because it allows you to define delimiter.

Syntax

=TEXTBEFORE(input_text, text_before, [n], [ignore_case]

where,

input_text = the text you are searching within. This function does not allow Wildcard characters.

text_before = the text that marks the point before which you want to extract. This is a required part of the formula.

[n]       The nth instance of text_before that you want to extract.  By default, n=1.  A negative number starts searching input_text from the end. Optional.

[ignore_case]       Specify FALSE to make the search case-sensitive. The default is TRUE which means the search is case-insensitive. Optional.

Example 1: Text before the first space:

Question: We want to know the text before the first space.

Answer: The formula : =TEXTBEFORE( B5, ” “), which returns the text “William“.

Example 2: Text before second space:

Question: We want to see the text before the second space.

Answer: The formula : =TEXTBEFORE(B5, ” “, 2), which returns the text “William Scott“.

Further reading:

Microsoft announces new Excel Functions

More readings on New Excel Functions:

TEXTAFTER

WRAPROWS

WRAPCOLS

VSTACK

HSTACK

TAKE

DROP