SEARCH function

SEARCH returns the numeric position of a substring within a text string, counting from a specified start point and ignoring case.

=SEARCH(find_text, within_text, [start_num])

Generate a SEARCH formula

Describe what you need. The generator will reach for SEARCH where SEARCH is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How SEARCH reads its arguments
find_textrequiredwithin_textrequiredstart_numoptionalSEARCH →
ArgumentRequiredDescription
find_textRequiredString – the substring you want to locate; if omitted or not found, SEARCH returns a #VALUE! error.
within_textRequiredString – the text to search inside; non-text values are coerced to text, but a pure error will still raise #VALUE!.
start_numOptionalOptional number – the character position to begin the search (default 1); must be ≥1 and ≤LEN(within_text) or #VALUE! is returned.

Returns

It returns a single numeric value indicating the 1-based character position of the first match.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Find the position of a keyword in a task

TaskOwnerStart DateDue DateHours Logged
Design mockupAlice2024-09-012024-09-0512
Backend APIBob2024-09-032024-09-1020
TestingCharlie2024-09-082024-09-1215
DocumentationAlice2024-09-102024-09-148
DeploymentBob2024-09-152024-09-165
=SEARCH("API", A3)

Result: 9

The cell A3 contains the text "Backend API". SEARCH looks for the substring "API" starting at the default position 1. The word "API" begins after the space, which is the ninth character, so the function returns 9. Because SEARCH is case-insensitive, it would also find "api" if the case differed.

2. Locate an owner name in a combined string

TaskOwnerStart DateDue DateHours Logged
Design mockupAlice2024-09-012024-09-0512
Backend APIBob2024-09-032024-09-1020
TestingCharlie2024-09-082024-09-1215
DocumentationAlice2024-09-102024-09-148
DeploymentBob2024-09-152024-09-165
=SEARCH("Alice", B2 & " - " & A2)

Result: 1

The formula concatenates the owner in B2 ("Alice") with a hyphen and the task name, producing "Alice - Design mockup". SEARCH then looks for "Alice" starting at the first character. Because the name appears right at the beginning, the function returns 1, indicating the match starts at position one.

3. Find month digits within a start date

TaskOwnerStart DateDue DateHours Logged
Design mockupAlice2024-09-012024-09-0512
Backend APIBob2024-09-032024-09-1020
TestingCharlie2024-09-082024-09-1215
DocumentationAlice2024-09-102024-09-148
DeploymentBob2024-09-152024-09-165
=SEARCH("09", C4, 6)

Result: 6

Cell C4 holds the date string "2024-09-08". By starting the search at character 6, SEARCH skips the year and the first hyphen, then looks for the substring "09". The month digits begin exactly at the sixth character, so the function returns 6. This technique is useful when you need to isolate parts of a formatted date without converting it to a true date value.

Common errors

Which SEARCH error are you seeing?
SEARCH returned an error#VALUE!
Verify the substring you are searching for actually appears, or wrap the call in IFERROR to supply an alternate result when the text is absent.
#VALUE!
Change the start_num to a valid positive integer such as 1, or omit the argument to default to the beginning of the string.
#VALUE!
Reduce start_num to a value less than or equal to LEN(within_text), or compute a dynamic start position using LEN or FIND to stay within bounds.
ErrorWhy it happensHow to fix it
#VALUE!The find_text "UI" does not exist anywhere inside the within_text "Design mockup", so SEARCH cannot locate a match.Verify the substring you are searching for actually appears, or wrap the call in IFERROR to supply an alternate result when the text is absent.
#VALUE!The optional start_num argument was set to 0, which is less than the required minimum of 1, causing SEARCH to reject the request.Change the start_num to a valid positive integer such as 1, or omit the argument to default to the beginning of the string.
#VALUE!A start_num of 50 was supplied while the within_text "Backend API" contains only 12 characters; the starting position exceeds the string length.Reduce start_num to a value less than or equal to LEN(within_text), or compute a dynamic start position using LEN or FIND to stay within bounds.

Tips and when to use something else

  • SEARCH ignores case; if you need a case-sensitive match, use FIND instead.
  • Combine SEARCH with MID to pull out the text that follows a known delimiter, e.g., =MID(A2, SEARCH("-", A2)+1, 10).
  • Remember that SEARCH is 1-based: the first character of any string is position 1, not 0.
  • When you need to locate whole words only, consider using TEXTBEFORE/TEXTAFTER with a delimiter or a regular-expression approach via FILTER, because SEARCH will also match substrings inside larger words.

Related functions

FINDFIND returns the numeric position of a substring within a text string, counting from a specified start point and respecting case sensitivity.LEFTLEFT returns the leftmost characters from a text string, optionally limited by a specified number of characters.MIDMID extracts a specified number of characters from a text string, starting at a given position, and returns the resulting substring.TEXTBEFORETEXTBEFORE returns the substring before a given delimiter, letting you pick which occurrence and supply your own value when the delimiter is absent.

Frequently asked questions

How do I make SEARCH case-insensitive?
SEARCH is inherently case-insensitive, meaning it treats "Apple" and "apple" as the same. If you ever need case-sensitive behavior, switch to the FIND function, which respects letter case.
Can SEARCH return more than the first occurrence of a substring?
No. SEARCH always returns the position of the first match it encounters. To find subsequent matches you can call SEARCH again, using a start_num that is one character beyond the previous result.
Why does SEARCH give #VALUE! even though the text looks present in the cell?
A common hidden cause is that the cell contains a numeric date or a formatted number, which Excel treats as a number. SEARCH expects text, so it coerces the value but may still error if the conversion fails. Convert the cell to text with TEXT or prepend an empty string (""&A2) before searching.
How can I extract the month from a date string using SEARCH?
First locate the month digits with SEARCH, for example =SEARCH("09", C4, 6) returns the start position of the month. Then wrap that in MID to pull out two characters: =MID(C4, SEARCH("09", C4, 6), 2). This yields "09", which you can later convert to a number with VALUE if needed.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator