FIND, FINDB functions

The FIND and FINDB functions in Excel are used to find the position of a substring within a text string. They return the starting position of the substring within the text. The key difference between them is that FIND operates on single-byte characters (standard characters), while FINDB is designed to work with double-byte characters (often used in languages like Chinese, Japanese, and Korean).

1. FIND Function

The FIND function returns the position of a substring within a text string, with case sensitivity. It cannot find the substring if the case doesn’t match.

Syntax:

=FIND(find_text, within_text, [start_num])

Parameters:

• find_text (required): The text you want to find (substring).
• within_text (required): The text string in which you want to search.
• start_num (optional): The position in the within_text where you want to start searching. The default is 1 (start from the first character).

Key Points:

• FIND is case-sensitive, meaning that it differentiates between uppercase and lowercase characters.
• It returns the position of the first character of the first occurrence of the substring within the text string. If the substring is not found, it returns an error (#VALUE!).
• The start_num parameter is optional. If omitted, the search begins at the start of the string.

Example Usage of FIND

1. Basic Usage

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

=FIND("World", A1)

This will return:

7

The word “World” starts at position 7 in the string “Hello World”.

2. Case Sensitivity

If you try to find “world” (lowercase) in the string “Hello World” (with an uppercase “W”), it will return an error because FIND is case-sensitive:

=FIND("world", A1)

This will return:

#VALUE!

3. Starting from a Specific Position

If you want to start the search from the 6th character onward in the text “Hello World”, you can use the start_num argument:

=FIND("World", A1, 6)

This will return:

7

It starts searching from the 6th character, and still finds “World” starting at position 7.

2. FINDB Function

The FINDB function works the same way as FIND, but it is used specifically with double-byte characters (DBCS), such as Chinese, Japanese, or Korean characters, where each character is represented by two bytes instead of one. FINDB counts the characters in terms of bytes, not individual characters.

Syntax:

=FINDB(find_text, within_text, [start_num])

Parameters:

• find_text (required): The text you want to find (substring).
• within_text (required): The text string in which you want to search.
• start_num (optional): The position in the within_text where you want to start searching (in bytes).

Key Points:

• FINDB works similarly to FIND, but the position is measured in bytes, not characters.
• FINDB should be used when working with text in double-byte character sets (DBCS), such as Asian languages. Each double-byte character is counted as two bytes.
• It can return an error (#VALUE!) if the substring is not found.

Example Usage of FINDB

1. Basic Usage with Double-Byte Characters

If cell A1 contains the string “こんにちは World” (Japanese text) and you want to find the position of the word “World”:

=FINDB("World", A1)

This will return:

13

In this example, because the Japanese text “こんにちは” consists of double-byte characters, the FINDB function treats those characters differently and returns the position in bytes.

2. Finding Double-Byte Characters

When working with double-byte characters, the position that FINDB returns will be different than what FIND would return because the bytes are counted differently.

For example, for the string “こんにちは World”, the word “World” starts at the 13th byte (after the 6 double-byte characters for “こんにちは”).

Key Differences Between FIND and FINDB

• Character vs Byte Counting:
  • FIND operates on characters (single-byte characters).
  • FINDB operates on bytes (used for double-byte characters).
• Use Case:
  • Use FIND for text with standard single-byte characters.
  • Use FINDB for text with double-byte characters, typically in languages like Chinese, Japanese, and Korean.

Notes:

• Error Handling: Both FIND and FINDB return #VALUE! if the substring is not found in the text string.
• Case Sensitivity: FIND is case-sensitive, while FINDB behaves the same way but in the context of DBCS characters.
• Start Position: You can specify a start_num to begin searching from a specific character or byte position in the text.

Related Functions:

• SEARCH and SEARCHB: These functions work similarly to FIND and FINDB, but are not case-sensitive.
  • SEARCH works with single-byte characters.
  • SEARCHB works with double-byte characters.
• MID and MIDB: These functions extract a substring from a text string, similar to FIND and FINDB but return the actual substring instead of the position.

The FIND and FINDB functions are powerful tools when working with text, especially for locating substrings, but understanding their distinction in terms of case sensitivity and character/byte handling is important when working with different types of text.