You can use the TEXTBEFORE function in Excel to extract all text in a cell before a specific character or substring.
This function uses the following syntax:
TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
where:
- text: Text to search
- delimiter: Character or substring to extract text before
- instance_num (optional): Instance of delimiter before which to extract text (default is 1)
- match_mode (optional): 0 = case-sensitive (default), 1 = case-insensitive
- match_end (optional): Treat end of text as delimiter (disabled by default)
- if_not_found (optional): Value to return if delimiter is not found
The following examples show the most common ways to use this function with the following dataset in Excel:
Example 1: Extract Text Before Substring
We can type the following formula into cell B2 to extract the text in cell A2 before the substring “is” is encountered:
=TEXTBEFORE(A2, "is")
We can then click and drag this formula down to each remaining cell in column B:
Column B contains the text in column A before the substring “is” is encountered.
Example 2: Extract Text Before First Space
We can type the following formula into cell B2 to extract the text in cell A2 before the first space is encountered:
=TEXTBEFORE(A2, " ")
We can then click and drag this formula down to each remaining cell in column B:
Column B contains the text in column A before the first space is encountered.
Example 3: Extract Text Before Nth Space
We can type the following formula into cell B2 to extract the text in cell A2 before the third space is encountered:
=TEXTBEFORE(A2, " ", 3)
We can then click and drag this formula down to each remaining cell in column B:
Column B contains all of the text in column A before the third space is encountered.
Note: You can find the complete documentation for the TEXTBEFORE function in Excel here.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Remove Specific Text from Cells
Excel: How to Remove Special Characters
Excel: How to Insert a Character into a String
