TEXTBEFORE

Formulas / TEXTBEFORE
Extract text before a delimiter.
TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
  • text - required, original text to be extracted
  • delimiter - required, character or string that delimits the text
  • instance_num - [OPTIONAL] number of the instance of the delimiter before which to extract text
  • match_mode - [OPTIONAL] determines the delimiter's case-sensitive behavior
  • match_end - [OPTIONAL] determines whether to search for the delimiter from the beginning or the end of the text
  • if_not_found - [OPTIONAL] determines what to return if the delimiter is not found

Examples

  • =TEXTBEFORE("Jones, Bob",",")

    The TEXTBEFORE function allows you to extract text from a string. For example, this formula returns everything in the string before the comma, which is "Jones". This can be useful when you have a string of text that needs to be broken up into separate pieces of information.

  • =TEXTBEFORE("ABX-112-Red-Y","-",1)

    The TEXTBEFORE function can also be used with multiple delimiters. For example, this formula returns "ABX-112", since this is the text before the first hyphen. This can be useful when you need to extract a specific piece of information from a string.

  • =TEXTBEFORE("ABX-112-Red-Y","-",2)

    The TEXTBEFORE function can also be used to extract multiple pieces of information from a string. For example, this formula returns "ABX-112-Red", since this is the text before the second hyphen. This can be useful when you need to extract multiple pieces of information from a string.

Summary

The TEXTBEFORE function is used to extract text from a given string that precedes a given character or string. The function has 6 arguments, including text, delimiter, instance_num, match_mode, match_end, and if_not_found, which can be used to customize the search. The function is case-sensitive and is the opposite of the TEXTAFTER function.

  • The TEXTBEFORE function returns all the text that comes before a given substring or delimiter.
  • The TEXTBEFORE function is used to extract text from a larger string.


Frequently Asked Questions

What is the TEXTBEFORE function?
The TEXTBEFORE function returns text that precedes a given character or string.
How is the TEXTBEFORE function different from the TEXTAFTER function?
The TEXTBEFORE function is the opposite of the TEXTAFTER function. The TEXTBEFORE function searches text from the beginning, while the TEXTAFTER function searches text from the end.
What does the TEXTBEFORE function return if text is an empty string?
The TEXTBEFORE function returns empty text if text is an empty string.
What does the TEXTBEFORE function return if instance_num is 0 or if instance_num is greater than the length of text?
The TEXTBEFORE function returns a #VALUE! error if instance_num is 0 or if instance_num is greater than the length of text.

Related Functions

Sourcetable Logo

Work smarter

Al is here to help. Leverage the latest models to
analyze spreadsheets, enrich data, and create reports.

Drop CSV