In Excel, the `SEARCH` and `SEARCHB` functions are used to find the position of a substring within a text string. Here’s how you can use them:
SEARCH Function
The `SEARCH` function finds the position of a substring within a text string, starting from a specified position. It’s case-insensitive, meaning it doesn’t differentiate between uppercase and lowercase letters.
Syntax:
SEARCH(find_text, within_text, [start_num])
- find_text: The text you want to find.
- within_text: The text string where you want to search for the substring.
- start_num (optional): Specifies the character at which to start the search. If omitted, it starts from the first character.
Example:
Suppose you have the text “Hello World” in cell A1, and you want to find the position of the substring “World”.
=SEARCH("World", A1)
This will return `7`, as “World” starts at the 7th position in the string “Hello World”.
SEARCHB Function
The `SEARCHB` function is similar to the `SEARCH` function but is primarily designed for use with double-byte character set (DBCS) languages, such as Chinese, Japanese, or Korean. It counts each double-byte character as 2 when the default language supports DBCS, while other languages count characters as 1 byte. The syntax and use are otherwise similar to `SEARCH`.
Syntax:
SEARCHB(find_text, within_text, [start_num])
Example:
If you work in a locale where DBCS is used, you can use `SEARCHB` the same way as `SEARCH`:
=SEARCHB("World", A1)
Key Points
- Case Insensitive: Both `SEARCH` and `SEARCHB` do not distinguish between upper and lower case.
- Wildcard Characters: You can use wildcard characters like `?` (which matches any single character) or `*` (which matches a sequence of characters) in the `find_text` argument.
- Error Handling: If the substring is not found, both functions return a `#VALUE!` error.
Practical Example with `SEARCH`:
Assume you have a list of email addresses in column A, and you want to extract the position of the “@” symbol.
=SEARCH("@", A2)
This formula will give you the position of “@” in each email address.
Always ensure that the text data you are working with is appropriate for the locale settings of the functions, particularly when using `SEARCHB`.
Unlock Your Potential
Excel
Access
Power BI
