SEARCH(), SEARCHB()

Syntax

Definition. The SEARCH() function returns the first occurrence at which a string is found. The search starts at the position indicated by the start_num argument. The function is not case-sensitive.

The SEARCHB() function returns the number of bytes of the characters. This function is used for double-byte strings.

Arguments

Background. If you edit text, you often need to know the position of a certain character or string within the text—for example, when you want to remove or replace the character. To do this, you use the SEARCH() or FIND()function.

Important

Unlike FIND() and FINDB(), the SEARCH() and SEARCHB() functions are not case-sensitive and allow the wildcard characters * and ?.

The SEARCH() function returns the character number at the start of the located text. The search text can include the wildcard characters ? and *. The question mark (?) stands for a character, and the asterisk (*) stands for a string. If you enter ? or *, the search string must be preceded by a tilde (~).

Use start_num to begin the search a specified number of characters into the string. For example, if you want to know the position of the first e after the initial code in the string XLS2003_FormatCellProtection, use 9 for start_num to skip the first part of the string. SEARCH() starts at the ninth character and returns the sixteenth character.

Note the following when working with this function:

Example. If you want to know whether the text in a column contains letters or numbers enclosed by square brackets, you can use a calculated column with a wildcard character search, as shown in Figure 8-11. The formula

=SEARCH(C25,D25)

returns position 5. The search looks for a string of any characters enclosed in square brackets. The string [small] starts at position 5 in the search string.

The string is found or the search returns an error.

Figure 8-11. The string is found or the search returns an error.

Here are some further examples:

See Also

FIND(), FINDB(), MID(), MIDB(), REPLACE(), REPLACEB(), SUBSTITUTE()

Sample Files

Use the SEARCH worksheet in the Text_Data.xls or Text_Data.xlsx sample file. The sample files are found in the Chapter08 folder. For more information about the sample files, see the section titled Using the Sample Files.