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