Excel TEXTBEFORE function - extract text before character (delimiter)

This tutorial explores Excel's TEXTBEFORE function, a powerful tool for quickly extracting text preceding a specified character or substring. Prior to its introduction, this task required complex formulas combining SEARCH/FIND and LEFT. TEXTBEFORE streamlines this process significantly.

• Understanding the TEXTBEFORE Function
• Extracting Text Before a Character
• Extracting Text Before the First Space
• Retrieving Text Before the Nth Instance of a Delimiter
• Obtaining Text Before the Last Occurrence of a Delimiter
• Extracting Text Before a Substring
• Handling Multiple Delimiters
• Customizing the Output for Missing Delimiters
• Returning the Original Text if a Delimiter is Absent
• Case-Sensitive and Case-Insensitive Matching
• Troubleshooting TEXTBEFORE Issues

The TEXTBEFORE Function in Excel

The TEXTBEFORE function efficiently returns text appearing before a designated character or substring (the delimiter). It handles multiple delimiter instances, allowing you to specify the desired occurrence. Furthermore, you can customize the output for scenarios where the delimiter is not found.

The function's syntax includes six arguments, with only the first two being mandatory:

TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])

• text (required): The source text string (or cell reference).
• delimiter (required): The character or substring marking the extraction point.
• instance_num (optional): The delimiter instance number. Defaults to 1. Negative numbers count from the end.
• match_mode (optional): Controls case sensitivity (0: case-sensitive, 1: case-insensitive). Defaults to 0.
• match_end (optional): Treats the end of the text as a delimiter (0: false, 1: true). Defaults to 0.
• if_not_found (optional): The value returned if the delimiter is absent. Defaults to #N/A.

TEXTBEFORE Availability

Currently, TEXTBEFORE is available in Microsoft 365, Excel 365 for Mac, and Excel for the web. Older Excel versions require alternative, more complex formulas. The TEXTAFTER function provides a counterpart for extracting text after a delimiter.

Extracting Text Before a Character

To extract text before a comma in cell A2:

=TEXTBEFORE(A2, ",")

Extracting Text Before the First Space

To extract text before the first space in A2:

=TEXTBEFORE(A2, " ")

To handle the Nth space, specify instance_num.

Retrieving Text Before the Nth Instance of a Delimiter

To extract text before the second comma in A2:

=TEXTBEFORE(A2, ",", 2)

A #N/A error results if the specified instance is not found. Use if_not_found to customize error handling.

Obtaining Text Before the Last Occurrence of a Delimiter

To extract text before the last comma in A2:

=TEXTBEFORE(A2, ",", -1)

Extracting Text Before a Substring

To extract text before " - " in A2 (handling hyphenated names):

=TEXTBEFORE(A2, " -")

Handling Multiple Delimiters

Use an array constant for multiple delimiter variations (e.g., {","," ,","-"," -"}):

=TEXTBEFORE(A2, {","," ,","-"," -"})

Customizing the Output for Missing Delimiters

To return "Not found" if the comma is absent:

=TEXTBEFORE(A2, ",", , , , "Not found")

To return a blank cell:

=TEXTBEFORE(A2, ",", , , , "")

Returning the Original Text if a Delimiter is Absent

Set match_end to 1 to return the original text if the delimiter is missing:

=TEXTBEFORE(A2, ",", , , 1)

Case-Sensitive and Case-Insensitive Matching

By default, TEXTBEFORE is case-sensitive. Set match_mode to 1 for case-insensitive matching:

=TEXTBEFORE(A2, "x", , 1)

Troubleshooting TEXTBEFORE Issues

• #N/A: Delimiter not found or instance_num exceeds occurrences.
• #VALUE!: instance_num is 0 or exceeds text length.

Downloadable Practice Workbook: TEXTBEFORE function – formula examples (.xlsx file)

The above is the detailed content of Excel TEXTBEFORE function - extract text before character (delimiter). For more information, please follow other related articles on the PHP Chinese website!