SEARCH() and SEARCHB() Functions in Excel

SEARCH() and SEARCHB() are two functions that aid in string manipulation within Excel. When you need to find the starting position of a substring within a text string without considering case sensitivity, employ SEARCH() or SEARCHB().

The following article outlines the syntax and usage of SEARCH() and SEARCHB() functions in Excel.

Description

SEARCH() and SEARCHB() Functions in Excel

SEARCH() and SEARCHB() functions search and locate the position of a substring within another text string. They return the result as the starting position of the substring from the first character in the other text string.

Syntax

=SEARCH(find_text, within_text, [start_num])

=SEARCHB(find_text, within_text, [start_num])

In this context:

- find_text: is the character or text you want the SEARCH() function to find.

- within_text: is the text string where you want to search for find_text.

- start_num: the position to start the search within_text.

Note

- SEARCH() and SEARCHB() functions search without case sensitivity.

- Start_num is ignored and defaults to 1.

- If start_num is less than 0 or greater than the length of within_text, the function returns an error value.

- If find_text is not found, the function returns an error value.

- You can use wildcard characters: question mark (?) and asterisk (*) in the find_text argument.

- Use start_num to skip a specified number of characters. The function always counts characters from the first character of within_text. If start_num is greater than 1, it includes the characters you skipped.

Example illustration

- Find the character 'p' in the within_text string starting from position 3.

- Search for the word 'memory' within the within_text string.

Additionally, you can combine it with other functions if you want to replace the searched text, such as using the REPLACE() and REPLACEB() functions to replace the located text, or using the MID() and MIDB() functions to return the text that was located.

For example: Replace 'mem' in the within_text string with 'cung'.

Apply the formula: =REPLACE(C6,SEARCH(B7,C6),3,'cung').

Now you know the syntax and how to use the SEARCH() and SEARCHB() functions. Depending on the specific string processing requirements, you can apply either the SEARCH() or SEARCHB() functions or combine them with other functions for optimal results. Wishing you success!