The SEARCH () function and the SEARCHB () function are two functions that help you process strings in Excel. When you need to find the starting position of a substring in a text string without case or case, you use the SEARCH () or SEARCHB () function.
The following article describes the syntax and usage of the SEARCH () and SEARCHB () functions in Excel.
Description
The SEARCH () and SEARCHB () functions search and position one substring string in another. Returns the result that is the starting position of the substring from the first character in another text string.
The SEARCHB () function counts 2 bytes per character when one of the languages that supports BDCS is used as the default language, otherwise the SEARCH function will count 1 byte per character.
Syntax
= SEARCH (find_text, within_text, [start_num])
= SEARCHB (find_text, within_text, [start_num]))
Inside:
- find_text: is the character, text that you want SEARCH () to find.
- within_text: text, string for you to find find_text.
- start_num: start position within within_text.
Note
- Search SEARCH () and SEARCHB () search are not case-sensitive.
- Start_num omitted will have the default value of 1.
- If start_num is less than 0 or greater than the length of within_text, the function returns the error value.
- If no value is found find_text will return an error value.
- You can use the wildcards: question mark (?) And asterisk (*) in find_text argument.
- Use start_num to skip a specified number of characters. The function always returns the number of characters from the first character of within_text, if start_num is greater than 1 the function will count the number of characters you ignore.
For example
- Find the character "p" in the string within_text starting at the 3rd position.
- Find the word "mem" in the string within_text.
In addition, you can combine with other functions if you want to replace the searched words such as REPLACE () and REPLACEB () to replace the text that has just been located, or use the MID () and MIDB () functions. to return the recently positioned text.
For example: Replace "mem" in string within_text with "arc".
Apply the formula: = REPLACE (C6, SEARCH (B7, C6), 3, "arc") .
So you know the syntax and usage of the SEARCH () and SEARCHB () functions. Depending on the different string handling requirements, you apply the SEARCH () function, the SEARCHB () function, or combine it with another function to get the most effect. Good luck!