All versionsExamples need Excel 2021 or laterText

FIND

VIND.ALLES in Dutch Excel

Returns the position at which one text first occurs inside another, matching upper and lower case exactly.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

ABC
1JournalDescriptionColon at
2VRKFACT 25-0412: Bakker Installatie BV1313=FIND(":", B2)=FIND(":"; B2)
3VRKCRN 25-0430: De Vries Bouw BV
4BNKBET 25-0412: Bakker Installatie BV
5MEMMEMO 25-0501: Hofstra Transport BV

Result: 13

=FIND(":", B2)
=FIND(":"; B2)

What FIND does and when to use it

What it does: it returns the position of the first character of the first match of a text inside another text, for example where the colon sits in a journal line description.

When to use it: when you need a position to cut at with LEFT, MID or RIGHT, or when case matters (the code X versus the dimension x). Use SEARCH if case should not matter or if you want wildcards.

How Excel evaluates it

Excel reads within_text and start_num (1 if omitted) and checks that start_num lies inside the text. From that position it compares find_text with each stretch of within_text, character by character, with upper and lower case treated as different.

At the first stretch that matches completely, it stops and returns the position of its first character, counted from the start of within_text, not from start_num. If it reaches the end without a match, it returns #VALUE!.

Watch out: When nothing matches, FIND returns #VALUE!, not 0. To test whether a text contains something, wrap FIND in ISNUMBER.

Mental model

A proofreader with a finger on the page who starts at a given letter, moves right, and calls out the column number where the exact word first appears; if it is not on the line, the proofreader says so with an error instead of a number.

Terms used on this page: wildcard

Syntax

Official FIND documentation
FIND(find_text, within_text, [start_num])
FIND(find_text; within_text; [start_num])
ArgumentTypeMeaningDefault
find_textRequiredThe text to look for. Case counts; * and ? are ordinary characters.
within_textRequiredThe text to search in; usually a cell reference.
start_numOptionalPosition to start searching from. Default 1. Use it to skip an earlier match.1

Return value:A number: the position of find_text in within_text, or #VALUE!

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 5 sheets, 16 KB)

Beginner

Find the colon in a journal line description

In the XAF auditfile export, the description of each journal line starts with a document code and entry number, then a colon and the relation: FACT 25-0412: Bakker Installatie BV. You want to split the entry number from the relation, and the first step is the position of the colon. The prefixes differ in length, so the position differs per row.

ABCD
1JournalDescriptionColon at
2VRKFACT 25-0412: Bakker Installatie BV1313=FIND(":", B2)=FIND(":"; B2)
3VRKCRN 25-0430: De Vries Bouw BV
4BNKBET 25-0412: Bakker Installatie BV
5MEMMEMO 25-0501: Hofstra Transport BV
6
Formula cell (C2)Input

Formula in C2

=FIND(":", B2)
=FIND(":"; B2)

Result: 1313

Step by step
  1. Excel reads find_text: a colon (:). It reads within_text: B2, FACT 25-0412: Bakker Installatie BV. start_num is left out, so the search starts at position 1.
  2. It compares character by character: F at 1, A at 2, C at 3, T at 4, the space at 5, 2 at 6, 5 at 7, the hyphen at 8, 0 at 9, 4 at 10, 1 at 11, 2 at 12, all no; the colon at 13, yes.
  3. It stops at the first match and returns 13. C2 shows 13: FACT 25-0412 takes positions 1 to 12, and the colon is the thirteenth character.
  4. Copy C2 down: CRN 25-0430 gives 12, BET 25-0412 gives 12, MEMO 25-0501 gives 13. The prefix CRN is one character shorter than FACT, so the colon moves one place to the left.
  5. The number is the input for the next step: LEFT(B2, C2 - 1), which is LEFT(B2, 12), returns the document and entry number FACT 25-0412.

Watch out: A description without a colon, for instance one typed by hand on a MEM entry, returns #VALUE!. That is FIND telling you the line does not follow the pattern, which is worth a look in itself.

More on this example: why it works, 1 more pitfall, 2 variations

Why it works

FIND turns a question about content (where is the colon?) into a number, and numbers are what the text-cutting functions take. Because it stops at the first match, it gives the end of the entry number even when the relation name contains a colon of its own.

More to watch out for

  • A description with a leading space shifts every position by one. Clean the export with TRIM before you search.

Variations

=LEFT(B2, FIND(":", B2) - 1)
=LEFT(B2; FIND(":"; B2) - 1)

FIND nested directly inside LEFT, without a helper column: returns FACT 25-0412.

=MID(B2, FIND(":", B2) + 2, 50)
=MID(B2; FIND(":"; B2) + 2; 50)

Everything after the colon and the space, the relation: Bakker Installatie BV.

Intermediate

Flag manual postings marked MAN

In journal entry testing, the client's bookkeepers type a capital MAN in the description of manual corrections. Ordinary words such as Management also contain the letters m, a, n, but in lowercase. You want a TRUE or FALSE column for manual postings, and a word like Management must not count.

ABCDE
1Entry no.DescriptionDebitManual?
225-0518Management fee Q3 Bakker€4,500.00€ 4.500,00FALSEFALSE=ISNUMBER(FIND("MAN", B2))=ISNUMBER(FIND("MAN"; B2))
325-0527MAN correction sales invoice 25-0412€1,210.00€ 1.210,00
425-0611Payroll March MAN posting€18,750.00€ 18.750,00
525-0702Office rent October€6,300.00€ 6.300,00
6
Formula cell (D2)Input

Formula in D2

=ISNUMBER(FIND("MAN", B2))
=ISNUMBER(FIND("MAN"; B2))

Result: FALSE

Step by step
  1. Excel evaluates FIND("MAN", B2) first. B2 is Management fee Q3 Bakker. The text starts with M, a, n, but the a and n are lowercase.
  2. FIND compares with case. Management is not MAN, and no other stretch of the description reads MAN in capitals either, so FIND returns #VALUE!.
  3. ISNUMBER receives #VALUE!, which is not a number, and returns FALSE. D2 shows FALSE: not a manual posting.
  4. Copied to D3, MAN correction sales invoice 25-0412 has MAN at position 1. FIND returns 1, ISNUMBER(1) is TRUE. In D4, Payroll March MAN posting has MAN at 15 (Payroll 1 to 7, space 8, March 9 to 13, space 14), TRUE. D5, Office rent October, has no MAN: FALSE.
  5. Replace FIND with SEARCH and D2 turns TRUE: SEARCH ignores case, finds Man at position 1 and marks a management fee as a manual posting. That is the difference this example depends on.

Watch out: A word that happens to contain MAN in capitals, such as a supplier called ROMANO, is counted as a manual posting. FIND only sees the letters; it cannot tell a marker from a coincidence. Check the TRUE rows once by eye.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

Case sensitivity is FIND's defining difference from SEARCH, and here it carries the audit rule: capital MAN means a manual correction, the lowercase letters in Management mean nothing. ISNUMBER converts FIND's two possible outcomes, a position or an error, into TRUE and FALSE, so the column can be filtered and counted in your journal entry test.

More to watch out for

  • Without ISNUMBER the column shows positions and #VALUE! errors, and a COUNTIF on it for TRUE finds nothing.

Variations

=ISNUMBER(FIND("MAN ", B2))
=ISNUMBER(FIND("MAN "; B2))

Search for MAN followed by a space if the marker is always a separate word; this also ignores MAN inside a longer capitalised word.

Advanced

Cut an entry number at the second hyphen

Entry numbers in the subledger export combine journal, year and sequence: VRK-25-0412. For a pivot per journal and year you need everything before the second hyphen. Journal codes differ in length, and a few opening balance entries have no hyphens at all and should stay as they are.

ABCD
1Entry no.AmountJournal and year
2VRK-25-0412€12,100.00€ 12.100,00
3BNK12-25-0003€3,025.00€ 3.025,00BNK12-25=IFERROR(LEFT(A3, FIND("-", A3, FIND("-", A3) + 1) - 1), A3)=IFERROR(LEFT(A3; FIND("-"; A3; FIND("-"; A3) + 1) - 1); A3)
4MEM-24-0019-€8,450.00€ -8.450,00
5OPENING€18,250.75€ 18.250,75
6
Formula cell (C3)Input

Formula in C3

=IFERROR(LEFT(A3, FIND("-", A3, FIND("-", A3) + 1) - 1), A3)
=IFERROR(LEFT(A3; FIND("-"; A3; FIND("-"; A3) + 1) - 1); A3)

Result: BNK12-25

Step by step
  1. Excel starts with the innermost FIND("-", A3). A3 is BNK12-25-0003: BNK12 fills positions 1 to 5, and the first hyphen is at 6. It returns 6.
  2. 6 + 1 = 7 becomes start_num for the outer FIND. The search for a hyphen now begins at position 7, the 2 of 25, so the hyphen at 6 is behind it and cannot be found again.
  3. From 7 it passes 2 (7) and 5 (8) and finds the second hyphen at 9. It returns 9, counted from the start of the text, not from 7.
  4. LEFT(A3, 9 - 1) is LEFT(A3, 8), which returns BNK12-25. No error, so IFERROR passes it through, and C3 shows BNK12-25.
  5. Copied to C2, VRK-25-0412 gives hyphens at 4 and 7 and returns VRK-25. In C4, MEM-24-0019 gives 4 and 7 and returns MEM-24.
  6. In C5, OPENING has no hyphen. The inner FIND returns #VALUE!, which flows through the outer FIND and LEFT. IFERROR catches it and returns A5 itself: OPENING.

Watch out: Forget the + 1 and the outer FIND starts on the first hyphen itself, finds it again at 6, and LEFT returns BNK12. It looks like a valid journal code and nothing signals the mistake.

More on this example: why it works, 2 more pitfalls, 1 variation

Why it works

FIND always returns the first match from where it starts, so the second occurrence is the first occurrence after the first one. Feeding the first position plus one into start_num moves the starting line past the first hyphen.

The result is still counted from the start of the text, which is why it can go straight into LEFT.

More to watch out for

  • A journal code with a hyphen inside, such as BNK-EUR-25-0003, returns BNK-EUR without any error. The formula assumes exactly two hyphens.
  • IFERROR returns the whole entry number for anything with fewer than two hyphens, including a malformed number like VRK-250412. Such a row lands in the pivot as if it were a valid group.

Variations

=LEFT(A3, FIND("#", SUBSTITUTE(A3, "-", "#", 2)) - 1)
=LEFT(A3; FIND("#"; SUBSTITUTE(A3; "-"; "#"; 2)) - 1)

SUBSTITUTE with instance_num 2 replaces only the second hyphen with a #, and FIND locates it. This scales to the third or fourth occurrence without nesting deeper.

MasterExcel 2021 or later

Match bank receipts to invoice numbers, skipping false hits

You reconcile the year-end bank statement to the receivables ledger. Customers pay with a free-text memo, and the invoice numbers look like INV26-0417. FIND is case-sensitive, so a lowercase inv in the memo is ignored, but one customer's own name starts with the capitals INV, and some memos contain no invoice number at all.

ABCD
1Bank memoReceivedInvoice
2Kavelmoor BV INV26-0417€1,210.00€ 1.210,00
3Tellinga inv INV26-0388 Q3€486.50€ 486,50
4INVESTA Holding INV26-0421€3,025.00€ 3.025,00INV26-0421=LET( memo, A4, first, FIND("INV", memo), pos, IF(ISNUMBER(--MID(memo, first + 3, 2)), first, FIND("INV", memo, first + 1)), IFERROR(MID(memo, pos, 10), "No invoice no.") )=LET( memo; A4; first; FIND("INV"; memo); pos; IF(ISNUMBER(--MID(memo; first + 3; 2)); first; FIND("INV"; memo; first + 1)); IFERROR(MID(memo; pos; 10); "No invoice no.") )
5Visser Bouw 2026-0402€750.00€ 750,00
6
Formula cell (C4)Input

Formula in C4

=LET(
  memo, A4,
  first, FIND("INV", memo),
  pos, IF(ISNUMBER(--MID(memo, first + 3, 2)), first, FIND("INV", memo, first + 1)),
  IFERROR(MID(memo, pos, 10), "No invoice no.")
)
=LET(
  memo; A4;
  first; FIND("INV"; memo);
  pos; IF(ISNUMBER(--MID(memo; first + 3; 2)); first; FIND("INV"; memo; first + 1));
  IFERROR(MID(memo; pos; 10); "No invoice no.")
)

Result: INV26-0421

Step by step
  1. memo is A4, INVESTA Holding INV26-0421, 26 characters. first is FIND("INV", memo): the capitals at the very start match, so first is 1.
  2. pos checks whether this is a real invoice number: the two characters after INV should be the digits of the year. MID(memo, 1 + 3, 2) is MID(memo, 4, 2), which is ES. --"ES" is #VALUE!, ISNUMBER returns FALSE.
  3. So IF takes its third argument: FIND("INV", memo, 1 + 1). The search starts at position 2 and skips the INV of INVESTA. INVESTA is 1 to 7, the space 8, Holding 9 to 15, the space 16, and INV starts at 17. pos is 17.
  4. MID(memo, 17, 10) takes the 10 characters of the invoice number format, INV26-0421. No error, so IFERROR passes it on and C4 shows INV26-0421.
  5. In C3, Tellinga inv INV26-0388 Q3: FIND skips the lowercase inv at 10, because it is case-sensitive, and returns 14. MID(memo, 17, 2) is 26, a number, so pos stays 14 and the result is INV26-0388. SEARCH would have stopped at the lowercase inv.
  6. In C5, Visser Bouw 2026-0402 has no INV. first is #VALUE!, the second FIND inherits the error through first + 1, pos is an error, MID is an error, and IFERROR returns No invoice no. That row goes to manual matching.

Watch out: The mirror-image mistake is start_num first instead of first + 1. The second FIND then starts on the I of INVESTA, finds it again at 1, and MID returns INVESTA Ho. It is 10 characters and starts with INV, so it looks like an invoice number in a quick scan.

More on this example: why it works, 2 more pitfalls, 1 variation

Why it works

Case sensitivity removes one class of false hits, lowercase words that contain inv. start_num removes another: when the first INV fails a plausibility test, the search restarts one character later and finds the next one. FIND reports positions from the start of the text, so pos can go straight into MID. LET names first and pos, and the one IFERROR at the end catches every failure path at once.

This handles one false hit per memo; do not use it when memos are longer and messier than that, because every extra false hit needs another nested FIND. The alternative is to ask the bank for the structured payment reference field, or use Power Query to split and match memos, at the cost of leaving the worksheet.

More to watch out for

  • Swapping FIND for SEARCH looks harmless and still works on these four rows, because the year test rejects the lowercase inv and the second search moves on. Add a memo with two lowercase false hits and SEARCH fails where FIND does not.
  • The fixed length of 10 assumes four-digit sequence numbers. From invoice 10000 onwards the last digit is cut off without any error.

Variations

=IFERROR(MID(A4, FIND("INV26-", A4), 10), "No invoice no.")
=IFERROR(MID(A4; FIND("INV26-"; A4); 10); "No invoice no.")

Search for a longer, more specific pattern instead of testing afterwards. Shorter, but the year is fixed in the formula and must be changed every January.

Common mistakes

SymptomCauseFix
#VALUE! on every row without a matchIF(FIND("x", A2) > 0, ...) returns #VALUE! for every row without an x, instead of the FALSE branch.The error passes through the comparison and IF. Test with ISNUMBER(FIND("x", A2)) instead.
#VALUE! although the word is in the cellFIND("total", A2) returns #VALUE! although A2 clearly says Total.FIND is case-sensitive, so total and Total are different. Use SEARCH if case should not matter.
#VALUE! with * in the search textYou used FIND("INV*", A2) to find any invoice reference and got #VALUE!.FIND has no wildcards; it looks for the literal characters INV followed by an asterisk. Search for INV alone, or use SEARCH, which supports * and ?.
A position higher than expectedWith start_num 5 you expected the position counted from 5, and got a bigger number.start_num only moves where the search begins. The result is always counted from the first character of the text, which is what LEFT, MID and RIGHT need.

Used in combinations

  • Find the customer from a bank descriptionCut the invoice number out of a bank statement description with TEXTAFTER and TEXTBEFORE, then look up the customer in the receivables list with XLOOKUP.Excel 2024 or later