Use Excel’s FIND function to locate a piece of text inside another cell and return the character position where it starts. For example, with the text INV-2026-047 in A2, enter =FIND("2026",A2) in another cell; the result is 5. FIND is case-sensitive, so it is useful when capitalization matters as well as when you need to split text or check whether a word appears.
You need the text to search for and a cell or text value to search within. The examples below use Microsoft Excel for Microsoft 365 on Windows. FIND is also available in Excel for Mac and Excel for the web; the formulas work the same way, though menus and keyboard behavior can vary.
What You’ll Need
- A workbook with the text you want to inspect, such as an invoice code in A2.
- A destination cell for the formula. If the search term changes, keep it in a separate cell so you can update it without editing the formula.
- Excel for Microsoft 365, Excel for the web, or another recent Excel version. FIND is an established Excel function and does not require a special add-in.
Step-by-Step
- Identify the text and the search term. Decide which characters you want Excel to locate and which cell contains the longer string. In the examples, A2 contains
INV-2026-047. The target may be a letter, a word, a space, or a punctuation mark. FIND searches within the cell’s text; it does not look for a value in a worksheet range the way a lookup function does. If your cells contain numbers or dates, Excel may convert their displayed values to text during the search, so check the actual cell contents and formatting when a result seems unexpected. - Choose a cell for the result and enter a basic FIND formula. With the text
INV-2026-047in A2, enter=FIND("2026",A2)in B2. Press Enter in Windows or Return on a Mac. Excel returns 5 because the first character in the match, 2, is the fifth character in the cell’s text. The position count starts at 1, not 0. Spaces and punctuation count as characters too, so the hyphen before 2026 is included in the count. - Use cell references when the search term may change. Put the text to find in B1 and the source text in A2. In C2, enter
=FIND(B1,A2). If B1 contains2026, the result is 5. This setup makes the formula easier to reuse: change B1 to a different search term and Excel recalculates the position. When copying the formula down a list, use the fill handle or copy and paste. If the search term should remain fixed while the source row changes, make its reference absolute by entering=FIND($B$1,A2); with B1 containing 2026 and A2 containing INV-2026-047, the result is still 5. - Make the search case-sensitive on purpose. FIND distinguishes uppercase from lowercase. With the word
Excelin A2, enter=FIND("E",A2)in B2; the result is 1. Enter=FIND("e",A2)instead and the result is 2, because the lowercase e is the second character. If capitalization should not matter, use Excel’sSEARCHfunction instead. For example, with Excel in A2,=SEARCH("e",A2)returns 1, the first matching e or E. - Start searching later in the text when needed. FIND has an optional third argument that tells Excel where to begin its search. With
INV-2026-047in A2, enter=FIND("-",A2,5)in B2. The result is 9, the position of the second hyphen. The search begins at character 5, but the returned position is still counted from the start of the original text. This is handy for locating a second delimiter without accidentally returning the first one. A start number of 1 searches from the beginning, just like leaving the argument out. - Use the position to extract text before a delimiter. Suppose A2 contains
INV-2026-047and you want the prefix before the first hyphen. In B2, enter=LEFT(A2,FIND("-",A2)-1). The result isINV. FIND returns the hyphen’s position, and subtracting 1 tells LEFT how many characters to return before it. This pattern works with a consistent separator such as a hyphen, slash, colon, or comma, provided that separator appears in every source value you process. - Extract the text after a delimiter. With
INV-2026-047in A2, enter=MID(A2,FIND("-",A2)+1,LEN(A2))in B2. The result is2026-047. FIND identifies the hyphen, adding 1 moves the starting point to the next character, and LEN supplies a length long enough to reach the end of the text. If you need the portion after the last delimiter rather than the first, this simple pattern is not sufficient by itself; you must first locate the last delimiter or use a different approach suited to your Excel version and data. - Test whether the search term exists instead of displaying its position. With a phrase in B1 and source text in A2, enter
=ISNUMBER(FIND(B1,A2))in C2. If B1 contains 2026 and A2 contains INV-2026-047, the result is TRUE. FIND returns a number when it finds a match, and ISNUMBER turns that into a logical test. If there is no match, FIND produces an error and ISNUMBER returns FALSE. Avoid leaving B1 blank: searching for an empty string can be treated as a match. If blank input should produce a blank result, use=IF(B1="","",ISNUMBER(FIND(B1,A2)))instead.
After entering a formula, check a few results against the source text before filling it through a large table. Excel recalculates formulas automatically in the normal calculation mode. If it does not update after you change the source or search term, check Formulas > Calculation Options and make sure Automatic is selected.
Common Mistakes
- Expecting FIND to ignore capitalization. FIND is case-sensitive. A search for
applewill not matchApple. Use SEARCH if the capitalization should be ignored, or standardize the text first if consistent capitalization is important for other reasons. - Assuming FIND supports wildcards. It does not treat an asterisk or question mark as a wildcard search pattern. FIND searches for the literal characters you give it. For a case-insensitive search, SEARCH is an alternative, but it also does not turn wildcard characters into FIND-style pattern matching.
- Counting from zero or forgetting spaces. Excel character positions begin at 1. A space, dash, comma, or other punctuation occupies a position just like a letter. To verify a result, count the characters in the cell from the left and include any spaces or separators.
- Using the wrong start number. The optional start number must be at least 1. A zero or negative value causes an error. A start number later than the text’s length will also fail to find a match. Remember that changing the start point does not reset the returned position to 1.
- Leaving an unhandled missing match in an extraction formula. If the delimiter is absent, FIND returns an error, which then causes LEFT or MID to return an error too. Either ensure the source data always contains the delimiter or handle missing cases explicitly, as shown in Troubleshooting.
Tips
- Put frequently changed search terms in their own cells rather than typing them directly into many formulas. That makes updates quicker and reduces the risk of inconsistent criteria.
- Use quotation marks around literal text in a formula, as in
=FIND("-",A2). When the search term is in a cell, refer to the cell without quotation marks. A quoted cell address is treated as text, not as a reference. - For a repeated delimiter, give FIND a start number based on the previous match plus one. This can help locate a later separator, but confirm the number is valid for every row because text lengths can vary.
- Use a clear output label such as Position or Has term above your formula column. A position result is a number, while a presence test returns TRUE or FALSE; labeling the column helps other people understand what the results mean.
- If your Excel uses a regional setting that expects semicolons between function arguments, replace the commas in the examples with semicolons. The function name and the search logic remain the same.
Troubleshooting
You see #VALUE!. This usually means the requested text was not found, the start number is less than 1, or the start point is beyond the text length. Check that the search term exactly matches the source, including capitalization, spaces, and punctuation. If you expect the text might be missing, wrap the search in IFERROR. With a hyphen search in A2, enter =IFERROR(FIND("-",A2),"Not found") in B2. If A2 contains INV2026047, the result is Not found instead of an error. Use a message that is clear to anyone reading the worksheet.
The result is a different position than expected. Check for leading spaces, hidden characters, or punctuation before the match. A cell can look as if it starts with a letter while actually containing a space first. You can inspect the source by clicking the cell and reviewing its contents in the formula bar. If text came from an external system, nonbreaking spaces or other unusual characters may be present; cleaning the source may be more reliable than changing the search formula.
The formula shows as text or does not recalculate. If Excel displays the formula characters instead of a result, the destination cell may be formatted as Text. Change its number format to General, then re-enter the formula by selecting the cell, pressing F2, and pressing Enter in Windows; on Mac, use the equivalent edit-and-confirm action for your keyboard. Also check whether Show Formulas is enabled on the Formulas tab. If the formula is visible but the result is stale, confirm that calculation is set to Automatic.
Your extraction formula breaks on some rows. Those rows may not contain the delimiter. Decide what the workbook should show when it is missing: a label, a blank, or the full original text. For example, with source text in A2, enter =IFERROR(LEFT(A2,FIND("-",A2)-1),A2) in B2. For INV-2026-047, the result is INV; for a value with no hyphen, the formula returns the original A2 value. Choose the fallback that best fits the task, and verify it does not hide data-quality problems you need to fix.
Frequently Asked Questions
What does FIND return in Excel?
FIND returns the numeric position of the first occurrence of the requested text inside another text value. The first character is position 1. If Excel cannot find the requested text, FIND returns a #VALUE! error rather than zero.
Is FIND case-sensitive?
Yes. FIND treats uppercase and lowercase letters as different characters. Choose SEARCH when you want the search to ignore case. Both functions return the position of a match and report an error when there is no match.
Can FIND search for a word in a whole column?
FIND works on a text value, commonly a single cell, rather than searching a range as a lookup operation. Enter a formula beside the first row and fill it down to test each row. If you only need TRUE or FALSE results, combine FIND with ISNUMBER.
Can I use FIND to locate the second occurrence of a character?
Yes. Set the optional start number to a position after the first occurrence. For example, starting the search at character 5 in INV-2026-047 finds the second hyphen at position 9. The returned number remains the position in the full original text.
Why does FIND return an error when the text looks like a match?
Common causes include different letter case, an extra space, a different punctuation mark, or a hidden character in copied data. Check the exact contents of both the search term and source cell. If the search should ignore case, use SEARCH; if a match is optional, handle a missing result with IFERROR.
Related
- How to Sum in Excel Using the Keyboard: Quick Steps
- How to Sum in Excel Without Hidden Rows Using SUBTOTAL
- Sum in Excel From Different Sheets: A Step-by-Step Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Weekday From Date: Formulas That WorkTo get the weekday for a date in Excel, use =WEEKDAY(A2,2) when the date is in cell A2;…
- Lowercase in Excel: Shortcut, Formula, and Fast FixesExcel has no built-in keyboard shortcut that directly changes selected text to lowercase. The dependable option is the…
- Excel Split Cell Function: Formulas, Tools, and TipsExcel does not have one universal “Split Cell” function, but you can separate a cell’s contents with Data…
- Excel Accounting Formulas Cheat Sheet for Daily BooksAn Excel accounting formulas cheat sheet starts with =SUM(range) for totals, a simple addition formula for running balances,…
- ROUND vs ROUNDUP in Excel: When to Use EachUse ROUND when you want a number rounded to the nearest place, and ROUNDUP when you need to…
- Insert a Row in Excel and Keep FormulasTo insert a row in Excel and keep formulas, select the row heading where the new row should…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.