Excel LEFT: How to Return Text Before a Specific Word

Formulas & functions · Updated November 19, 2026

To return everything before a particular word in an Excel cell, use =TEXTBEFORE(A2,B2) in Microsoft 365, where A2 contains the text and B2 contains the word or phrase to find. In older Excel versions, use =LEFT(A2,FIND(B2,A2)-1). Both approaches omit the matched word; the best choice depends on your Excel version and how you want to handle spaces or missing matches.

What “left of a word” means in Excel

Excel does not have a single function named LEFT OF WORD. The phrase usually means returning the characters that appear before a chosen word or phrase in a cell. For example, if A2 contains Send the parcel tomorrow, you might want the result Send the parcel when the target is tomorrow.

This is a two-part operation: Excel must locate the target, then return the characters before its position. The LEFT function returns a specified number of characters starting at the beginning of text. FIND identifies where a target begins. TEXTBEFORE combines those jobs in one function, which is why it is usually simpler in current Excel.

The result is text, even if the extracted characters look like a number or date. If you plan to calculate with the result, you may need to convert it to the appropriate data type afterward. Also, extracting text before a word is different from splitting a cell at every space: these formulas use a particular word or delimiter as the stopping point.

Use TEXTBEFORE in Microsoft 365

TEXTBEFORE is the most direct option in Microsoft 365 and Excel 2024. It returns the part of a text value that comes before a specified delimiter. The delimiter can be a word, a phrase, or punctuation. For example, with the full text in A2 and the target in B2, enter =TEXTBEFORE(A2,B2). If A2 contains Maple Street, Apt 4 and B2 contains ,, the result is Maple Street.

TEXTBEFORE does not include the delimiter in its result. Characters before the delimiter are otherwise preserved, including spaces. To control the result, include a space in the delimiter when that suits the data. With the text Mira at home in A2, the formula =TEXTBEFORE(A2," at ") returns Mira, without the space before the target. If the delimiter were just at, the returned text would include the space before it.

In a worksheet, enter the formula in the cell where you want the answer, then fill or copy it down for other rows. A cell reference for the target, such as B2, makes a formula easier to reuse than typing a different target into every formula. When copying down, Excel normally adjusts relative references: A2 becomes A3, and B2 becomes B3.

Use LEFT and FIND in older Excel versions

If your Excel version does not include TEXTBEFORE, combine LEFT with FIND. Assume A2 contains Order 4821 shipped and you want the text before shipped. Enter =LEFT(A2,FIND("shipped",A2)-1). FIND returns the starting position of the target; subtracting one gives the number of characters to keep; LEFT returns that many characters from the beginning. The result is Order 4821 , including the space immediately before the target.

That trailing space is a frequent surprise, not a calculation error. To remove spaces from the beginning and end of the extracted result, wrap the expression in TRIM. With the same text in A2, use =TRIM(LEFT(A2,FIND("shipped",A2)-1)); the result is Order 4821. TRIM also reduces repeated internal spaces to single spaces, so use it only if that change is acceptable for your data.

If the search word is in B2 rather than typed into the formula, the older-version pattern is =LEFT(A2,FIND(B2,A2)-1). This is useful when each row has its own target. FIND is case-insensitive, so it finds uppercase or lowercase versions of the target. It returns the first match, not necessarily a whole word; a target such as art can also match the letters in carton.

Choose a delimiter that gives the right boundary

A target word by itself can be ambiguous. If you want to match a complete word rather than the same letters inside a longer word, use surrounding spaces where the data allows. For example, the delimiter and distinguishes that word from a longer string containing the letters and. Punctuation may be part of the useful boundary too: a comma and a following space can mark the start of a location or list item.

For a sentence containing We deliver on Friday, if weather permits, the phrase , if may be a better delimiter than the common word if if you want to keep the comma and everything before it. The appropriate delimiter depends on the desired result: TEXTBEFORE removes the entire delimiter, so include only the characters you are willing to exclude.

TEXTBEFORE is case-sensitive by default. If the text in A2 is was delivered today but the target in B2 is DELIVERED, a default TEXTBEFORE search will not treat those as a match. With the text in A2, use =TEXTBEFORE(A2,"DELIVERED",1,1) to set match_mode to 1, which makes the match case-insensitive; the result is was . For the older LEFT and FIND method, FIND is already case-insensitive. SEARCH is another older function for locating text and, unlike FIND, supports wildcard characters.

Return text before the first or last match

By default, TEXTBEFORE returns text before the first occurrence of the delimiter. Its optional instance_num argument lets you choose a different occurrence. A positive number counts from the beginning; a negative number counts from the end. This can help when cells contain repeated separators.

For example, assume A2 contains Portland, Maine, USA and you want everything before the final comma and space. Use =TEXTBEFORE(A2,", ",-1). The result is Portland, Maine. With the default occurrence instead, TEXTBEFORE would return only Portland, because that is the text before the first comma and space.

Check which occurrence you actually need before filling a formula down a column. Inconsistent text can make the same occurrence number produce results with different meanings. If some rows have one delimiter and others have several, consider whether the last delimiter consistently marks the boundary you want.

Handle missing words and unexpected results

When TEXTBEFORE cannot find its delimiter, it returns a #N/A error by default. FIND also returns an error when it cannot find the search text, and that error flows through a LEFT and FIND formula. It is worth deciding what a missing match should mean: perhaps the whole original cell should be returned, perhaps the result should be blank, or perhaps the error should remain visible so the data can be corrected.

If A2 contains the source text and you want to keep the original value when the delimiter – is absent, use =IFERROR(TEXTBEFORE(A2," - "),A2). If the delimiter exists, Excel returns the text before it; if the formula raises an error, IFERROR returns A2. This is convenient for mixed data, but it can also hide other errors, so do not use it automatically when errors should be investigated.

A blank result is a different choice from returning the original text. With the source in A2 and the target in B2, use =IFERROR(LEFT(A2,FIND(B2,A2)-1),"") if a missing target should produce an empty cell. The empty text between the quotation marks is intentional. If a target appears at the very beginning, the part before it is also empty, which may be correct rather than an error.

Other common causes of surprising output include hidden spaces, inconsistent punctuation, and a target that appears more than once. If a formula appears not to match, inspect the source cell and target for leading or trailing spaces. A delimiter containing a space will not match a cell that uses a different spacing pattern. Also confirm whether the data uses a hyphen, en dash, or another similar-looking character.

Version availability and practical alternatives

TEXTBEFORE is available in Microsoft 365 and Excel 2024. It is not available in every older perpetual Excel release, so a formula using it may show a #NAME? error in an unsupported version. LEFT and FIND are established functions and are a practical compatibility choice when a workbook must work in older editions. If coworkers use different versions, consider testing the workbook in the oldest version they need to support.

For a one-time extraction, you can also use Excel’s text-splitting tools, but they are less suited to locating an arbitrary word that changes from row to row. Text to Columns is designed to divide data using a delimiter across selected cells, while formulas recalculate when the source changes and can use a target stored in another cell. Power Query is another option for repeatable data-cleaning tasks, especially when importing and reshaping larger tables. For a simple, live result beside each source row, a formula is generally easier to maintain.

Choose the approach based on the job:

  • Use TEXTBEFORE for a short, readable formula in Microsoft 365 or Excel 2024.
  • Use LEFT with FIND when compatibility with older Excel versions matters.
  • Use a delimiter that includes suitable spaces or punctuation when whole-word boundaries matter.
  • Use IFERROR only after deciding what a missing target should return.
  • Use a repeatable data tool such as Power Query when the task is part of a larger import-and-clean process.

Frequently Asked Questions

How do I extract everything before a word in Excel?
In Microsoft 365 or Excel 2024, use TEXTBEFORE with the source cell and target word, such as =TEXTBEFORE(A2,B2). In older Excel versions, use LEFT with FIND: =LEFT(A2,FIND(B2,A2)-1).

How do I keep the space before the word?
Use the target word alone as the delimiter. The formula returns all characters before it, including any space immediately before the word. To omit that space, include it in the delimiter or apply TRIM if removing extra spaces throughout the result is acceptable.

Can TEXTBEFORE ignore capitalization?
Yes. Its match_mode argument can make the search case-insensitive. Set that argument to 1, as in =TEXTBEFORE(A2,"WORD",1,1). Without that setting, TEXTBEFORE is case-sensitive by default.

What if the word is not in the cell?
TEXTBEFORE returns #N/A if it cannot find the delimiter, while the LEFT and FIND pattern also returns an error. Use IFERROR if you want a deliberate fallback, such as the original text or an empty result, rather than an error.

Does LEFT find a whole word automatically?
No. LEFT only returns characters from the start of text, and FIND locates a sequence of characters. To avoid matching the target inside another word, choose a delimiter with suitable spaces or punctuation, and check that the source text uses those boundaries consistently.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.