SEARCH, SEARCHB functions

The SEARCH and SEARCHB functions in Excel are used to find the position of a substring within a larger text string. These functions are useful for locating where specific text starts within a string. The main difference between the two functions lies in how they count the characters: SEARCH is based on character count, while SEARCHB is based on byte count. This distinction is important when dealing with languages that use double-byte characters, such as Japanese, Chinese, or Korean.

1. SEARCH Function

The SEARCH function finds the position of a substring within a larger text string, returning the starting position of the first occurrence of the substring.

Syntax:

=SEARCH(find_text, within_text, [start_num])

Parameters:

• find_text (required): The substring or text you want to find.
• within_text (required): The text string where you want to search for find_text.
• start_num (optional): The position in the within_text string where the search will begin. If omitted, the search starts from the beginning (position 1).

Key Points:

• The SEARCH function is case-insensitive, meaning it will ignore whether the characters are uppercase or lowercase.
• If the substring is not found, the function returns the #VALUE! error.
• The search can be done with wildcards:
  • * (asterisk) to represent any number of characters.
  • ? (question mark) to represent a single character.

Example Usage:

1. Find the Position of a Substring

If cell A1 contains the text “Hello World” and you want to find the position of the word “World”:

=SEARCH("World", A1)

This will return:

7

Explanation:

• The word “World” starts at position 7 in “Hello World,” so the function returns 7.

2. Case-Insensitive Search

If cell B1 contains “Data Analysis” and you want to find “analy” (case-insensitive):

=SEARCH("analy", B1)

This will return:

6

Explanation:

• The search is case-insensitive, so it finds “analy” starting from position 6 in “Data Analysis.”

3. Using the Optional start_num Argument

If you want to start searching from the 6th character onward in cell C1 containing “Data Science”, the formula:

=SEARCH("Science", C1, 6)

This will return:

6

Explanation:

• The search starts at position 6, and it finds “Science” starting at that position.

2. SEARCHB Function

The SEARCHB function is similar to SEARCH, but it operates on bytes rather than characters. This distinction is useful when working with double-byte character sets (DBCS), such as languages like Japanese, Chinese, or Korean, where each character may occupy more than one byte.

Syntax:

=SEARCHB(find_text, within_text, [start_num])

Parameters:

• find_text (required): The substring or text you want to find.
• within_text (required): The text string where you want to search for find_text.
• start_num (optional): The position in the within_text string where the search will begin, similar to SEARCH.

Key Points:

• SEARCHB is based on byte count instead of character count.
• It is important to use SEARCHB when working with languages that use double-byte characters. This way, each character is treated as a byte, which can affect the results in languages like Japanese.

Example Usage:

1. Find the Position of a Substring (Byte-based)

If cell D1 contains the text “こんにちは” (which uses 10 bytes in UTF-8 encoding), and you want to find the position of “に”:

=SEARCHB("に", D1)

This will return:

3

Explanation:

• The function counts the position based on byte length, and “に” starts at byte position 3 in “こんにちは.”

2. Byte-based Search in a Text with Mixed Characters

If cell E1 contains the text “ABCこんにちは” (which has 8 bytes in total) and you want to find the position of “こ”, the formula:

=SEARCHB("こ", E1)

This will return:

4

Explanation:

• The SEARCHB function counts bytes and finds “こ” starting at byte position 4.

Key Differences Between SEARCH and SEARCHB:

• Character vs Byte:
  • SEARCH: Operates on characters, meaning it counts characters in single-byte character sets.
  • SEARCHB: Operates on bytes, meaning it counts bytes. It is useful when working with double-byte characters (such as in East Asian languages like Chinese, Japanese, or Korean).
• Language Impact:
  • In languages using single-byte characters, both SEARCH and SEARCHB will function similarly. However, in DBCS (double-byte character sets), SEARCH and SEARCHB may return different results because SEARCHB counts byte positions (which may differ from character positions).
• Wildcards: Both SEARCH and SEARCHB support wildcards (* and ?), allowing for flexible search patterns.

Example Comparison:

If cell F1 contains the Japanese text “こんにちは世界” (which uses 14 bytes in UTF-8 encoding), we can see how SEARCH and SEARCHB behave differently:

1. Using SEARCH to find “に”:

=SEARCH("に", F1)

This will return:

2

Explanation:

• The function counts characters and finds “に” at position 2.

2. Using SEARCHB to find “に”:

=SEARCHB("に", F1)

This will return:

4

Explanation:

• The function counts bytes and finds “に” at byte position 4, as the text “こんにちは世界” is encoded with double-byte characters.

Notes:

• Case-Insensitive Search: Both SEARCH and SEARCHB are case-insensitive.
• Handling Missing Substrings: If the substring is not found, both functions will return the #VALUE! error.
• Negative or Zero start_num: If start_num is less than or equal to 0, the function will start searching from the beginning of the string.

Related Functions:

• FIND / FINDB: Like SEARCH and SEARCHB, but case-sensitive. FIND works with characters, while FINDB works with bytes.
• MID / MIDB: Extract a substring from a text string, similar to how SEARCH locates positions.

The SEARCH and SEARCHB functions are powerful tools for locating text within a string. Use SEARCH for single-byte text and SEARCHB when dealing with double-byte characters, particularly in non-Latin languages.