MID() extracts text from the middle of a string.

Syntax:
=MID(text,start_num,num_chars)
Suppose:

EMP-001-IND

You want:

001

Use:
=MID(A2,5,3)
Result:

001

Because:

Start at character 5

Extract 3 characters

🔟 FIND()

FIND() tells you where one piece of text appears inside another.

Example:

john.smith@gmail.com

You can find the position of @:
=FIND("@",A2)
This returns the position of the @ character.

Why is this useful?

You can use the position to extract:

• Email username

• Domain

• Product components

• Codes

• Identifiers

1️⃣1️⃣ SEARCH()

SEARCH() is similar to FIND() but has some differences.

For example:
=SEARCH("india",A2)
Unlike FIND(), SEARCH() is not case-sensitive.

Simple distinction:

FIND() → Case-sensitive

SEARCH() → Not case-sensitive

This difference can matter when cleaning real-world data.

1️⃣2️⃣ SUBSTITUTE()

SUBSTITUTE() replaces specific text with another value.

Suppose:

A2 = Mumbai, India

You want to replace the comma with a hyphen.
=SUBSTITUTE(A2,",","-")
Result:

Mumbai- India

You can also replace words.
=SUBSTITUTE(A2,"India","IND")
Result:

Mumbai, IND

1️⃣3️⃣ CONCAT()

CONCAT() combines text.

Suppose:

First Name | Last Name

John | Smith

Formula:
=CONCAT(A2," ",B2)
Result:

John Smith

This is useful when you need to create:

• Full names

• IDs

• Labels

• Descriptions

1️⃣4️⃣ TEXTJOIN()

TEXTJOIN() is particularly useful when combining multiple values with a delimiter.

Example:

Suppose:

A2 = John

B2 = Smith

C2 = India

Formula:
=TEXTJOIN(", ",TRUE,A2:C2)
Result:

John, Smith, India

The second argument:

TRUE

tells Excel to ignore empty cells.

1️⃣5️⃣ TEXTSPLIT()

Modern Excel includes TEXTSPLIT(), which is extremely useful for breaking text into multiple columns.

Suppose:

A2 = John,IT,Pune

Use:
=TEXTSPLIT(A2,",")
Excel can split it into:

John | IT | Pune

This is particularly useful when data arrives in a delimited format.

1️⃣6️⃣ Extract an Email Username

Suppose:

A2 = john.smith@gmail.com

You want:

john.smith

Using modern Excel:
=TEXTBEFORE(A2,"@")
Result:

john.smith

1️⃣7️⃣ Extract an Email Domain

Using the same data:

john.smith@gmail.com

Use:
=TEXTAFTER(A2,"@")
Result:

gmail.com

These modern text functions can make data preparation much easier.

1️⃣8️⃣ Combining Text Functions

The real power comes from combining functions.

Suppose your data contains:

"  JOHN SMITH  "

You want:

John Smith

You could use:
=PROPER(TRIM(A2))
First:

TRIM() removes unnecessary spaces.

Then:

PROPER() formats the name.

Result:

John Smith

1️⃣9️⃣ Real-World Data Cleaning Example

Suppose your department column contains:

IT

IT

it

IT

It

These values may represent the same department.

You could standardize them with:
=UPPER(TRIM(A2))
Results become:

IT

IT

IT

IT

IT

Now filtering, counting and lookups become much more reliable.

2️⃣0️⃣ Data Quality Check Using Text Functions

Suppose all employee IDs should contain exactly 6 characters.

You can use:
=IF(LEN(A2)=6,"Valid","Check")
If:

A2 = EMP001

Result:

Valid

If:

A2 = EMP01

Result:

Check

This is a simple example of using Excel for data-quality validation.

🧪 Practical Interview Challenge