Find and Replace Functions

Search Functions

SEARCH (search_text, text, start_position): Locates the search_text within the text string, and returns the character position of the first occurrence. The character position to begin the search can be specified (start_position); if start_position is omitted, the search will begin at the first character in the text string.  The SEARCH function is not case-sensitive (e.g., "A" and "a" are considered to be the same). The SEARCH function allows you to use special search keys:

?

will find any single character

*

will find any character group

FIND (search_text, text, start_position): Locates the search_text within the text string, and returns the character position of the first occurrence. The character position to begin the search can be specified (start_position); if start_position is omitted, the search will begin at the first character in the text string. Unlike the SEARCH function, the FIND function is case-sensitive (e.g., "A" and "a" are considered to be different), and it does not use special search keys (? and *).

Examples:

Formula

Result

=SEARCH("h", "Hello")

1

=FIND("h", "Hello")

#VALUE! (search_text not found)

=FIND("a", "character")

3

=FIND("a", "character", 4)

5

=SEARCH("April 20??", "April 2002")

1

=FIND("April 20??", "April 2002")

#VALUE! (search_text not found)

=SEARCH("*.ess", "Book1.ess")

1

=FIND("*.ess", Book1.ess")

#VALUE! (search_text not found)

=SEARCH("?", "Why?")

1

=FIND("?", "Why?")

4

 

Replace Functions

SUBSTITUTE (text, old_text, new_text, instance): Replaces the old_text within the text string with the new_text. The instance of the old_text in the string to replace can be specified.

REPLACE (orig_text, start_position, num_chars, repl_text): Replaces part of the orig_text string with another text string (repl_text).

 

Byte Functions

EasySpreadsheet includes search and replace functions that use bytes rather than characters. Standard characters and symbols are one byte, while some foreign language characters (e.g., Chinese) are two bytes.

SEARCHB (search_text, text, start_position): Locates the search_text within the text string, and returns the byte position of the first occurrence. The byte position to begin the search can be specified (start_position); if start_position is omitted, the search will begin at the first byte in the text string. The SEARCHB function works in the same way as the SEARCH function.

FINDB (search_text, text, start_position): Locates the search_text within the text string, and returns the byte position of the first occurrence. The byte position to begin the search can be specified (start_position); if start_position is omitted, the search will begin at the first byte in the text string. The FINDB function works in the same way as the FIND function.

REPLACEB (orig_text, start_position, num_bytes, repl_text): Replaces part of orig_text string with another text string (repl_text). The REPLACEB function works in the same way at the REPLACE function.

 

See Also:

Find and Replace