📊 Excel Basics #26 – LEN(), FIND() & SEARCH() Functions

When working with real-world data, text is often messy.

You may need to count characters, find specific words, or locate symbols inside text.

That's where LEN(), FIND(), and SEARCH() become useful.

📌 1. LEN() Function

LEN() counts the number of characters in a text string.

Syntax:

=LEN(text)

Example:

=LEN("Excel") → Result: 5

Spaces are also counted.

=LEN("Data Analyst") → Result: 12

📌 2. FIND() Function

FIND() returns the position of one text string inside another.

Syntax:

=FIND(find_text, within_text, [start_num])

Example:

=FIND("@","rahul@gmail.com") → Result: 6

The "@" symbol appears at position 6.

⚠️ FIND() is case-sensitive.

=FIND("A","Data") finds uppercase "A". Searching for lowercase "a" gives a different result.

📌 3. SEARCH() Function

SEARCH() also finds the position of text inside another text string.

Syntax:

=SEARCH(find_text, within_text, [start_num])

Example:

=SEARCH("analyst","Data Analyst") → Result: 6

Unlike FIND(), SEARCH() is not case-sensitive.

So =SEARCH("ANALYST","Data Analyst") also returns: 6

📌 FIND() vs SEARCH()

FIND():

• Case-sensitive

• Does not support wildcards

• Useful when exact capitalization matters

SEARCH():

• Not case-sensitive

• Supports wildcards such as ** and ?

• Useful for flexible text searches

📌 Real-World Example

Suppose: A2 = "rahul.sharma@gmail.com"

Find the position of "@":

=FIND("@",A2) → Result: 13

Count the total characters:

=LEN(A2)

Use with LEFT(), RIGHT(), or MID() to extract parts.

To extract everything before "@":

=LEFT(A2,FIND("@",A2)-1) → Result: rahul.sharma

📌 Common Mistake

If FIND() or SEARCH() cannot find the text, Excel returns: #VALUE!

Handle it using:

=IFERROR(SEARCH("@",A2),"Not Found")

📌 Real-World Uses

• Find "@" in email addresses

• Locate hyphens or separators in IDs

• Count characters in customer names

• Extract usernames from email addresses

• Clean and transform raw datasets

• Identify whether specific text exists within a cell

Remember:

LEN() → How many characters?

FIND() → Where is it? Case-sensitive

SEARCH() → Where is it? Not case-sensitive

These functions become even more powerful when combined with LEFT(), RIGHT(), MID(), and IFERROR().

Double Tap ❤️ For More