🚀 Excel Formulas Fundamentals — Part 8

📝 Text Functions for Data Cleaning & Manipulation

In real-world datasets, text data is often inconsistent. Names may have extra spaces, different letter cases, or be combined into one field.

Text functions help you clean, extract, combine, and standardize text efficiently.

📌 These functions are heavily used by Data Analysts, Business Analysts, HR teams, and Finance professionals.

🧠 1. LEN() – Count Characters
LEN() returns the total number of characters in a cell, including spaces.
Syntax: =LEN(text)
Example: =LEN(A2)
If A2 contains Deepak
Result: 6
📌 Use Cases: Validate IDs, Check password length, Verify product codes

⬅️ 2. LEFT() – Extract Characters from the Left
Extracts a specified number of characters from the beginning of a text string.
Syntax: =LEFT(text,num_chars)
Example: =LEFT(A2,3)
If A2 = "Laptop"
Result: Lap
📌 Useful for extracting prefixes or department codes.

➡️ 3. RIGHT() – Extract Characters from the Right
Returns characters from the end of a text string.
Syntax: =RIGHT(text,num_chars)
Example: =RIGHT(A2,4)
If A2 = "INV2026"
Result: 2026
📌 Useful for extracting years, invoice numbers, or suffixes.

🔍 4. MID() – Extract Characters from the Middle
Extracts text starting from a specified position.
Syntax: =MID(text,start_num,num_chars)
Example: =MID(A2,3,4)
If A2 = "EMP12345"
Result: P123
📌 Useful for extracting parts of employee IDs or product codes.

✂️ 5. TRIM() – Remove Extra Spaces
Removes leading, trailing, and extra spaces between words.
Syntax: =TRIM(A2)
Example:
Before: John Doe
After: John Doe
📌 Essential for cleaning imported data.

🔠 6. UPPER(), LOWER(), PROPER()
These functions standardize text case.

UPPER()
=UPPER(A2)
Result: JOHN DOE

LOWER()
=LOWER(A2)
Result: john doe

PROPER()
=PROPER(A2)
Result: John Doe
📌 Useful for customer names, addresses, and reports.

🔗 7. CONCAT() and & – Combine Text
Using &
=A2&" "&B2
If A2 = John, B2 = Doe
Result: John Doe

Using CONCAT()
=CONCAT(A2," ",B2)
📌 Combines multiple text values into one.

📋 8. TEXTJOIN()
Joins text with a chosen delimiter.
Syntax: =TEXTJOIN(", ",TRUE,A2:A5)
Example:
Names: Rahul, Priya, Amit
Result: Rahul, Priya, Amit
📌 Useful for generating email lists or summaries.

🔄 9. SUBSTITUTE()
Replaces specific text with new text.
Syntax: =SUBSTITUTE(text,old_text,new_text)
Example: =SUBSTITUTE(A2,"Yes","Approved")
If A2 = "Yes"
Result: Approved
📌 Great for standardizing values.

🛠️ 10. REPLACE()
Replaces text based on position.
Syntax: =REPLACE(old_text,start_num,num_chars,new_text)
Example: =REPLACE("ABC123",1,3,"XYZ")
Result: XYZ123
📌 Useful when the position of the text is fixed.

🔎 11. FIND() and SEARCH()
Both functions return the position of text inside another text string.

FIND() Case-Sensitive
=FIND("Excel",A2)

SEARCH() Case-Insensitive
=SEARCH("excel",A2)
Example: If A2 = "Learn Excel Today"
Result: 7
📌 Useful for checking whether a keyword exists in a text string.

🎯 Mini Practice Project
Create: Customer_Data.xlsx
Data:
First Name | Last Name | Full Name | Customer ID
john | doe | CUST-2026-001

Tasks:
✅ Convert names to Proper Case
=PROPER(A2)

✅ Create Full Name
=A2&" "&B2

✅ Remove Extra Spaces
=TRIM(C2)

✅ Extract Year from Customer ID
=MID(D2,6,4)

✅ Count Characters in Customer ID
=LEN(D2)

🏆 End of Part 8
After completing this lesson, you should be able to:
✅ Extract text using LEFT(), RIGHT(), and MID()
✅ Clean data using TRIM()
✅ Standardize text with UPPER(), LOWER(), and PROPER()
✅ Combine text using CONCAT(), TEXTJOIN(), and &
✅ Replace and modify text using SUBSTITUTE() and REPLACE()