Excel SEARCH function

Summary

The Excel SEARCH function finds the position of one text string within another, ignoring case. SEARCH is often used in text parsing and data manipulation, especially when the case of the characters is not important or consistent.
Syntax
				
					=SEARCH(find_text, within_text, [start_num])
				
			
  • find_text: The text to find
  • within_text: The text within which to search
  • start_num: [Optional] The position in the text to start the search. Defaults to 1
Return value
The position of the first character of the found text within the text string. Returns #VALUE! if the text is not found.

How to use

SEARCH is useful for locating the position of a substring within a larger text string, particularly in scenarios where the case sensitivity of the text is not a concern. It’s commonly used for data extraction and validation tasks.

Examples

Simple SEARCH
Finding the Position of a Substring: To locate the position of a substring:
				
					=SEARCH("apple", A1)
				
			
Finds the position of “apple” in the text in A1, regardless of case.
SEARCH with Start Position
Specifying a Start Position in Search: Finding a substring after a certain position:
				
					=SEARCH("n", C2, 4)
				
			
Searches for “n” in C2 starting from the 4th character.
SEARCH in Data Validation
Validating Email Format: Checking for the presence of “@” in an email address:
				
					=IF(ISNUMBER(SEARCH("@", D2)), "Valid Email", "Invalid Email")
				
			
Validates whether D2 contains an “@” symbol.
SEARCH Combined with MID
Extracting a Substring Using SEARCH: Using SEARCH with MID to extract text:
				
					=MID(E2, SEARCH(":", E2) + 1, LEN(E2))
				
			
Extracts text after “:” in E2.

Additional Notes

  • SEARCH is case-insensitive, unlike the FIND function which is case-sensitive.
  • Can be combined with other text functions for complex data manipulations.
  • Returns an error if the find_text is not found in within_text.

Related Functions

Excel FIND function

The Excel FIND function locates one text string within another, returning the start position, crucial for text analysis.

Excel SUBSTITUTE function

The Excel SUBSTITUTE function replaces text in a string, ideal for altering data or removing unwanted characters.

Excel REPLACE function

The Excel REPLACE function substitutes part of a text string with a different text string, ideal for data correction and manipulation.

Content Navigation