These functions are useful for finding and modifying text.

FIND

Purpose

Finds the position of text within another text string.

It is case-sensitive.

Syntax

=FIND(find_text,within_text,[start_num])

Example:

saroj@gmail.com

Formula:

=FIND(“@”,A2)

Result:

6

SEARCH

SEARCH also finds the position of text but is not case-sensitive.

Syntax

=SEARCH(find_text,within_text,[start_num])

Example:

=SEARCH(“gmail”,A2)

SUBSTITUTE

Replaces specific text with new text.

Syntax

=SUBSTITUTE(text,old_text,new_text,[instance_num])

Example:

Sircl Tech Pvt Ltd

Formula:

=SUBSTITUTE(A2,”Pvt Ltd”,”Private Limited”)

Result:

Sircl Tech Private Limited

REPLACE

Replaces characters based on their position.

Syntax

=REPLACE(old_text,start_num,num_chars,new_text)

Example:

EMP-1001

Formula:

=REPLACE(A2,5,4,”2026″)

Result:

EMP-2026

Practice

Create a Product Code Cleaning sheet and use:

  • FIND
  • SEARCH
  • SUBSTITUTE
  • REPLACE

to identify and modify codes.

PreviousNext