All versionsExamples need Excel 2021 or laterText

SEARCH

VIND.SPEC in Dutch Excel

Returns the position at which one text first occurs inside another, ignoring upper and lower case and allowing the wildcards ? and *.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

ABC
1Entry no.DescriptionStarts at
225-0731Invoice CORRECTION 25-041299=SEARCH("correction", B2)=SEARCH("correction"; B2)
325-0745Correction of accrual Q3
425-0802Office rent October

Result: 9

=SEARCH("correction", B2)
=SEARCH("correction"; B2)

What SEARCH does and when to use it

What it does: it returns the position of the first match of a word or pattern in a text, ignoring upper and lower case, for example where correction starts in a journal description.

When to use it: when you scan free text and cannot rely on how people typed it. It understands the wildcards ? and *. Use FIND when a capital letter matters or a ? must be read as itself; wrap SEARCH in ISNUMBER to test whether a word occurs.

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, treating upper and lower case as the same, letting ? match any one character and * match any run of characters, including none.

At the first stretch that fits completely it stops and returns the position of its first character, counted from the start of within_text. If Excel reaches the end without a fit, the result is #VALUE!.

Watch out: A ? or * in the search text is a wildcard, not a character, and a misplaced one matches silently instead of failing. A ~ in front of it makes the next character literal, so ~? finds a real question mark.

Mental model

Ctrl+F in a document with match case switched off, except that you get the character number of the first hit instead of a highlight, and ? and * stand in for letters you are unsure about.

Terms used on this page: wildcard

Syntax

Official SEARCH documentation
SEARCH(find_text, within_text, [start_num])
SEARCH(find_text; within_text; [start_num])
ArgumentTypeMeaningDefault
find_textRequiredThe text to look for. Case is ignored; ? and * are wildcards, ~ escapes them.
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, 17 KB)

Beginner

Find where the word correction starts, in any case

You are testing journal entries at Veldhuis Installatie BV. The bookkeepers type the word correction in the description in whatever case they like. As a first step you want to know at which position the word starts, without caring about capitals.

ABCD
1Entry no.DescriptionStarts at
225-0731Invoice CORRECTION 25-041299=SEARCH("correction", B2)=SEARCH("correction"; B2)
325-0745Correction of accrual Q3
425-0802Office rent October
5
Formula cell (C2)Input

Formula in C2

=SEARCH("correction", B2)
=SEARCH("correction"; B2)

Result: 99

Step by step
  1. Excel reads find_text: correction, in lowercase. within_text is B2, Invoice CORRECTION 25-0412. start_num is left out, so the search starts at position 1.
  2. It compares from position 1 and ignores case. Invoice fills positions 1 to 7. At position 6 the c matches the first letter of correction, but the e after it does not match the o that should follow, so Excel moves on.
  3. The space is position 8. From position 9 the letters CORRECTION fit correction, one by one: C-O-R-R-E-C-T-I-O-N is 10 letters, and capitals count as lowercase. Excel stops here.
  4. It returns 9, the position of the first letter of the match. C2 shows 9: Invoice and the space take 8 characters, so the word starts at the ninth.
  5. Copy C2 down. Correction of accrual Q3 gives 1, because the word is the first thing in the text. Office rent October gives #VALUE!, because the word is nowhere in it. FIND would also return #VALUE! for the first row, since it sees CORRECTION in capitals as a different word.

Watch out: SEARCH finds the letters wherever they occur, so a description such as No correction needed also counts as a hit for correction. Look at the matching rows before you rely on the column.

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

Why it works

SEARCH compares letters without caring whether they are capitals, so one search text covers every way the bookkeepers might have typed the word. The result is a position counted from the first character of the description, which is the form LEFT, MID and REPLACE expect.

More to watch out for

  • The #VALUE! on the third row is not a broken formula; it means the word is absent. The Intermediate example shows how to turn that into a number you can count.

Intermediate

Wildcard * to spot a manual posting that is later reversed

In journal entry testing at Veldhuis Installatie BV you look for manual postings that carry a reversal. The bookkeepers tag the description with man (manual) first and rev (reversal) later, in upper or lower case, with an account number between the two. Only descriptions where man comes before rev matter; a rev in front of man is a different situation.

ABCD
1Entry no.DescriptionPattern at
225-0701JV man 4500 rev Q344=IFERROR(SEARCH("man*rev", B2), 0)=IFERROR(SEARCH("man*rev"; B2); 0)
325-0702JV REV 4500 man Q3
425-0703JV MAN 4510 REV
525-0704JV 4500 Q3
6
Formula cell (C2)InputNote: JV is the journal voucher prefix, man marks a manual posting and rev a reversal. The descriptions are short codes and are not translated.

Formula in C2

=IFERROR(SEARCH("man*rev", B2), 0)
=IFERROR(SEARCH("man*rev"; B2); 0)

Result: 44

Step by step
  1. Excel evaluates SEARCH first. find_text is man*rev: the letters man, then * for any run of characters, then rev. within_text is B2, JV man 4500 rev Q3.
  2. J is position 1, V is 2, the space is 3. The letters man fill positions 4 to 6, so the pattern can start at 4.
  3. The * takes the space, 4500 and the next space (positions 7 to 12), and rev follows at 13 to 15. The whole pattern fits from 4 to 15, and SEARCH returns where the match starts: 4, not 13.
  4. There is no error, so IFERROR passes the 4 through and C2 shows 4.
  5. In C3, JV REV 4500 man Q3 has rev at position 4, but the pattern needs man first. man sits at 13 and nothing after it reads rev (only Q3), so SEARCH returns #VALUE! and IFERROR turns it into 0. A plain SEARCH("rev", B3) would have returned 4 for this row.
  6. In C4, JV MAN 4510 REV has MAN at 4 and REV at 13; capitals make no difference, so the result is 4. C5, JV 4500 Q3, has neither tag and gives 0.

Watch out: The * accepts anything between the two tags, so man*rev also matches JV manual accrual, see review: man in manual, rev in review. Add spaces or use longer tags if the descriptions contain ordinary words.

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

Why it works

The * lets one search text express an order: man somewhere before rev. Without a wildcard you would need two SEARCH functions and a comparison of their positions.

The match starts where man starts, which is why the result is 4 and not 13. IFERROR changes the error for no match into 0, so the column holds only numbers.

More to watch out for

  • Without IFERROR the column mixes positions and #VALUE! errors, and a SUM or MAX over it returns an error too.

Variations

=ISNUMBER(SEARCH("man*rev", B2))
=ISNUMBER(SEARCH("man*rev"; B2))

TRUE or FALSE instead of a position, when you only need to filter the flagged rows.

=IFERROR(SEARCH("man ???? rev", B2), 0)
=IFERROR(SEARCH("man ???? rev"; B2); 0)

Each ? stands for exactly one character, so this accepts only a four-character reference (such as 4500) between man and rev, each surrounded by one space.

Advanced

Cut a description at a literal question mark

At Veldhuis Installatie BV the bookkeepers type a ? in the description when they are unsure which account a posting belongs on. For your list of open questions you want the text before the ?, without the trailing space. Descriptions without a ? should show a clear message instead of an error.

ABCD
1Entry no.DescriptionOpen question about
225-0911Prepaid insurance Q4 ? account unclearPrepaid insurance Q4=IFERROR(TRIM(LEFT(B2, SEARCH("~?", B2) - 1)), "No question mark")=IFERROR(TRIM(LEFT(B2; SEARCH("~?"; B2) - 1)); "No question mark")
325-0912Fuel cards November
425-0913Legal fees ?? to be checked
5
Formula cell (C2)Input

Formula in C2

=IFERROR(TRIM(LEFT(B2, SEARCH("~?", B2) - 1)), "No question mark")
=IFERROR(TRIM(LEFT(B2; SEARCH("~?"; B2) - 1)); "No question mark")

Result: Prepaid insurance Q4

Step by step
  1. Excel starts with the innermost SEARCH("~?", B2). The ~ is not compared; it tells Excel to read the ? as an actual question mark. B2 is Prepaid insurance Q4 ? account unclear.

  2. Counting the characters:

    • Prepaid is 1 to 7
    • a space is 8
    • insurance is 9 to 17
    • a space is 18
    • Q4 is 19 and 20
    • a space is 21

    The question mark is at 22, and SEARCH returns 22.

  3. 22 - 1 = 21, so LEFT(B2, 21) takes the first 21 characters: Prepaid insurance Q4 followed by the space at position 21.
  4. TRIM removes that trailing space and returns Prepaid insurance Q4. There is no error, so IFERROR passes it on and C2 shows Prepaid insurance Q4.
  5. In C3, Fuel cards November has no question mark. SEARCH returns #VALUE!, which flows through the subtraction, LEFT and TRIM. IFERROR catches it and C3 shows No question mark. In C4, Legal fees ?? to be checked has its first ? at 12, so the result is Legal fees.
  6. Now write SEARCH("?", B2) without the ~. The ? is a wildcard for any single character and fits position 1, so SEARCH returns 1, LEFT(B2, 0) is empty text, and C2 shows nothing at all, without any error.

Watch out: Forgetting the ~ is the silent failure: SEARCH("?", B2) returns 1 for every non-empty description, so the whole column fills with empty text and looks like a clean result. If the column of open questions is suspiciously blank, check for the ~ first.

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

Why it works

~? switches the wildcard off for one character, so SEARCH treats the question mark as a character like any other and returns its position. LEFT cuts at that position minus 1, TRIM cleans the edge, and IFERROR turns the no-match error into text a colleague can read.

Evaluated inside out, each function receives exactly the value the one inside it produced.

More to watch out for

  • Only the first ? counts. In Legal fees ?? to be checked the second one is ignored, and a description that starts with a ? returns empty text, because there is nothing before it.

Variations

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

FIND has no wildcards, so a plain ? is already a literal question mark and needs no ~. The trade-off is that FIND is case-sensitive, which does not matter for a question mark.

=SEARCH("~?", B2, SEARCH("~?", B2) + 1)
=SEARCH("~?"; B2; SEARCH("~?"; B2) + 1)

Uses start_num to skip the first question mark and returns the position of the second one. It gives #VALUE! when there is no second ?.

=SEARCH("~*", B2)
=SEARCH("~*"; B2)

The same escape works for a literal asterisk, for example when auditors mark checked lines with *. Use ~~ to find a tilde itself.

MasterExcel 2021 or later

Earliest keyword hit from a list, with wildcards and a blank guard

The audit team keeps a keyword list for journal descriptions at Veldhuis Installatie BV:

  • correction
  • year*end
  • error
  • manual
  • a spare row for later additions

For each description you want the keyword that appears first in the text, not first in the list, so the person checking your work sees what the bookkeeper led with. The spare row is blank today and must not be reported as a hit.

ABCDE
1Entry no.DescriptionFirst keywordKeywords
225-1001Manual year-end correctionmanual=LET( memo, B2, words, $D$2:$D$6, hits, IFERROR(SEARCH(words, memo) / (words <> ""), 99999), first, MIN(hits), IF(first = 99999, "No keyword", INDEX(words, MATCH(first, hits, 0))) )=LET( memo; B2; words; $D$2:$D$6; hits; IFERROR(SEARCH(words; memo) / (words <> ""); 99999); first; MIN(hits); IF(first = 99999; "No keyword"; INDEX(words; MATCH(first; hits; 0))) )correction
325-1002Cleaning contract Q2year*end
425-1003Correction of an error, see year end
5error
6manual
7
Formula cell (C2)InputNote: D4 is the blank spare row. In a real sheet the list would grow downwards and the formula range would cover the whole list.

Formula in C2

=LET(
  memo, B2,
  words, $D$2:$D$6,
  hits, IFERROR(SEARCH(words, memo) / (words <> ""), 99999),
  first, MIN(hits),
  IF(first = 99999, "No keyword", INDEX(words, MATCH(first, hits, 0)))
)
=LET(
  memo; B2;
  words; $D$2:$D$6;
  hits; IFERROR(SEARCH(words; memo) / (words <> ""); 99999);
  first; MIN(hits);
  IF(first = 99999; "No keyword"; INDEX(words; MATCH(first; hits; 0)))
)

Result: manual

Step by step
  1. LET names two inputs: memo is B2, Manual year-end correction, and words is $D$2:$D$6, the five keyword cells (correction, year*end, blank, error, manual). Nothing is calculated yet.

  2. SEARCH(words, memo) is evaluated once per keyword and returns five positions.

    Manual year-end correction: manual fills 1 to 6, the space is 7, year-end is 8 to 15, the space is 16, correction starts at 17. So the positions are:

    • correction gives 17
    • year*end gives 8
    • the blank cell gives 1 (empty text fits at the start)
    • error gives #VALUE!
    • manual gives 1
  3. words <> "" gives TRUE, TRUE, FALSE, TRUE, TRUE. Dividing the positions by it leaves the numbers alone (divided by 1) and turns the blank row into 1 / 0, which is #DIV/0!. The list is now 17, 8, #DIV/0!, #VALUE!, 1.
  4. IFERROR replaces both errors with 99999, a number larger than any text length. hits is 17, 8, 99999, 99999, 1. first is MIN(hits), which is 1.
  5. first is not 99999, so IF takes its third argument. MATCH(1, hits, 0) finds the 1 in the fifth place, and INDEX(words, 5) returns manual. The keyword that is last in the list wins, because it is first in the text.
  6. For C3, Cleaning contract Q2 fits no keyword, all five values become 99999 and C3 shows No keyword. For C4, Correction of an error, see year end puts correction at 1, error at 18 and year*end at 29, so the result is correction.

Watch out: Drop the division by (words <> "") and the blank row in D4 silently wins. Its position is 1, MATCH finds it before manual, and INDEX returns 0 from an empty cell. The formula still looks fine and reports a keyword that does not exist.

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

Why it works

SEARCH accepts a whole range as find_text and returns one position per keyword, so a single call tests the entire list. Wildcards live in the list itself, so year*end catches year-end, year end and yearend without extra formulas. The 99999 turns errors into a number that MIN ignores in practice, and dividing by (words <> "") disarms blank cells, which would otherwise match at position 1.

Do not use this construction on tens of thousands of rows against a long list: every row runs every keyword, and the sheet recalculates slowly. For that size, Power Query with a case-insensitive Text.Contains per keyword is the better tool, at the cost of a refresh step outside the worksheet.

This formula needs LET and dynamic arrays, which exist in Excel 2021 and Microsoft 365; older versions do not have LET at all.

More to watch out for

  • The opposite mistake is FIND instead of SEARCH. It runs without errors, but misses every keyword typed with a different case than the list: Manual does not fit manual, so C2 would pick correction at 17 instead of manual at 1. The result looks plausible and is wrong.
  • Lock the keyword list as $D$2:$D$6. With a relative D2:D6, copying C2 down shifts the range to D3:D7 in C3 and D4:D8 in C4: the first keywords drop out of the test and empty cells below the list join it. Nothing shows an error, but the keywords reported are wrong.
  • A wildcard keyword can reach further than intended: year*end also fits a description such as Year 2025 audit, weekend work, because * spans anything between year and end. Test new wildcard keywords on a sample before adding them to the list.

Variations

=SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$6, B2)), --($D$2:$D$6 <> ""))
=SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$6; B2)); --($D$2:$D$6 <> ""))

Counts how many keywords occur in the description, not which comes first. Prefer it when the number of red flags per entry is the question.

Common mistakes

SymptomCauseFix
Position 1 on every rowSEARCH("?", A2), meant to find a question mark, returns 1 on every row, even rows without one.An unescaped ? is a wildcard for any single character, so it fits the first character of every non-empty text. Write SEARCH("~?", A2); the same goes for ~* and ~~.
Unwanted hits such as reviewSearching for rev to find reversals also flags review and revenue, and searching for cor flags Corporate.SEARCH looks for the characters, not for whole words. Put spaces around the search text (" rev "), or use a longer, more specific word.
#VALUE! on rows without the wordIF(SEARCH("x", A2) > 0, ...) returns #VALUE! for every row without an x instead of taking the FALSE branch.No match is an error, not 0. Use ISNUMBER(SEARCH(...)) for TRUE or FALSE, or IFERROR(SEARCH(...), 0) when you want a number.
A blank cell looks like a hitSEARCH(A2, B2) returns 1 on a row where A2 is empty, so a blank search cell looks like a hit at the very start.Empty find_text is treated as empty text, which fits at start_num. Test the search cell first, for example with IF(A2 = "", ...).

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