FIND in Excel: character positions and corrected formulas

When I want to extract part of a product code or the text before a separator, I first need the separator’s position. That is what FIND provides: a character number that another formula can use.

I will start with short strings, where an error of one character is easy to spot. Then I will cover first, subsequent, and last occurrences, followed by extracting a file name. The examples are synthetic. Syntax and availability were checked against Microsoft documentation, while character positions and string operations were checked with a JavaScript helper. The formulas were not executed in Excel; the helper does not verify a particular workbook or its regional settings.

FIND syntax and the meaning of its result

The English examples use commas, while the Polish version uses semicolons. Argument separators depend on regional settings. Function names and separators are separate details to match to your installation.

=FIND(find_text, within_text, [start_num])

Square brackets indicate an optional argument in this syntax description; I do not type them into a working formula. The first argument specifies the fragment to find, the second provides the text to search, and the third specifies where searching begins. Omitting the starting position means 1. Character numbering also begins at 1.

The function distinguishes uppercase and lowercase letters. It searches for a sequence of characters, without checking whether that sequence forms a whole word. The result identifies the start of the match in the entire string, even when searching begins at a later position. These rules appear in the FIND documentation.

Case sensitivity in the word Excel

I write out Excel: uppercase E is at position 1, x at 2, c at 3, lowercase e at 4, and l at 5. That simple breakdown is enough to check these results.

Uppercase E exists in the string and should be found. Uppercase X produces an error because the source contains lowercase x. I do not automatically convert data to lowercase to remove an error: first, I establish whether letter case is part of an identifier’s meaning.

If the intended search should ignore case, SEARCH is the appropriate alternative. That is a deliberate choice of matching behaviour, rather than a fix for every situation where FIND returns an error.

Starting positions and subsequent occurrences

In Experience, lowercase e appears at positions 4, 7, and 10. The first letter is uppercase E, so it does not match lowercase e even when searching from the beginning.

The result 7 in the second row is a position in the entire word Experience. It is not a distance from position 5. This distinction matters when combining the result with MID.

To find the second space in A1 containing a b c, I use:

=FIND(" ",A1,FIND(" ",A1)+1)

The inner function returns 2. Adding 1 starts the next search at position 3, producing a final result of 4. Without that addition, I would find the same space again. For a multi-character separator, if matches must not overlap, I would advance by the separator’s length.

The Polish sentence Excel to potężne narzędzie! provides another useful position check. Its first t is at 7, potężne starts at 10, and searching for t from position 10 returns 12. Spaces count just like letters. I include them when checking a result by hand.

Extracting text before a separator

I enter ola@example.test in A1 without quotation marks. The @ character is at position 4. To retrieve the preceding three characters, I subtract 1 from that position.

=LEFT(A1,FIND("@",A1)-1)

The expected result is ola. This demonstrates splitting text; it is not an email address validator. It does not verify domain existence, delivery, or every permitted address format. If the data contains more than one separator, the function uses the first match.

For a recurring import, I would separately define how to handle an empty cell, a missing @, and a separator at the beginning. In the last case, the requested length is zero and the result is empty text. That can resemble missing data even though it comes from a specific string structure.

I retain the source column beside the extracted result. If someone reports an incorrect split, comparing the two values makes the case reproducible. Removing the original immediately after extraction makes it harder to determine whether the problem comes from the formula or an unexpected input format.

FIND versus SEARCH

In FIND, an asterisk is a literal character. For example, =FIND("*","ab*cd") should return 3. In SEARCH, an asterisk can stand for a sequence of characters and a question mark for one character. Use ~* to search for a literal asterisk with that second function. These rules are documented for SEARCH.

I do not substitute one function for the other mechanically. If a separator comes from a cell and contains an asterisk, switching to SEARCH can change the meaning of the search. I first decide whether the input represents literal text or a pattern, then choose control examples for that decision.

The last space: break the formula into steps

FIND has no argument meaning “search backwards.” For A1 containing a b c, I can count spaces, replace the last one with a unique marker, and locate that marker. Here I choose #, which is absent from the input.

=FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))

The arithmetic is straightforward: a b c has length 5, while abc has length 3. The difference, 2, is the number of spaces. SUBSTITUTE replaces the second space, creating a b#c. The # character is at position 4. The separator count is used as an occurrence number, not as a starting character position.

The fourth argument of SUBSTITUTE selects a particular occurrence. Without it, all spaces would be replaced. This differs from REPLACE, which uses a position and a character count. In Polish Excel, SUBSTITUTE is PODSTAW, REPLACE is ZASTĄP, and LEN is DŁ; LICZBA.ZNAKÓW is not the function name for this operation. See the SUBSTITUTE documentation.

This construction has assumptions: the separator is one character, it occurs at least once, and the marker does not already appear earlier in the string. For a# b c, the marker collides with the data and the search returns the wrong position. For abc, the occurrence count is zero and the missing separator needs handling. Those cases prevent this from being a universal formula for arbitrary strings.

A file name after the last backslash

I enter C:\Data\report.xlsx in A1. The last \ is at position 8, so the file name starts at 9. For this controlled path, I can reuse the previous approach and extract the remaining text.

=MID(A1,FIND("#",SUBSTITUTE(A1,"\","#",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,LEN(A1))

The expected result is report.xlsx. In an Excel formula, "\" represents a single backslash. I do not double it as I might in some programming languages. The assumptions about a present separator and an absent # marker still apply.

I also avoid assuming that every path ends with a file name. When the final character is a separator, the extracted part is empty. That case deserves separate treatment if a later step requires a specific name. Paths containing mixed separator styles need an agreed normalisation rule first.

Shorter formulas in Microsoft 365 and Excel 2024

When recipients have a supported version, TEXTAFTER can retrieve text after the last separator without a temporary marker. A negative occurrence number starts searching from the end.

=TEXTAFTER(A1,"\",-1)

For the same path, the expected result is report.xlsx. If I need the position of the last space in a b c, I can use the length of the text before it:

=LEN(TEXTBEFORE(A1," ",-1))+1

For this second formula, A1 must again contain a b c; the result is 4. Microsoft lists these functions for Microsoft 365 and Excel 2024. I do not claim availability in Excel 2021. See TEXTAFTER and TEXTBEFORE. Their default missing-separator error is #N/A, which differs from the missing-match error returned by FIND.

Errors, empty values, and checking the output

A missing match returns #VALUE!, shown as #ARG! in Polish Excel. The same error type can result from a starting position of zero or a position greater than the string length. I check the arguments before wrapping the formula in error handling.

=IFERROR(FIND("@",A1),"No match or invalid data")
=ISNUMBER(FIND("@",A1))

The first formula returns an explanation instead of an error; the second returns TRUE or FALSE. TRUE only confirms that the character was found, not that the entire address is valid. Error handling should retain the information needed to repair an import.

An empty search string is a separate case: with an ordinary nonempty input, FIND("","Excel") returns 1. If the search comes from a cell, I ensure that an accidental blank does not count as finding a required fragment. The examples here cover ordinary letters, Polish characters, and separators; the helper check does not cover every complex Unicode sequence.

Finally, I take several formats from the intended process, replace their contents with synthetic data, and write down the expected outputs. I include no separator, two separators, empty text, and a trailing separator. That small set is easier to assess than a long formula viewed without its input. For working with substring lengths, also see the separate guide to LEN.