📊 Excel Formulas — Part 5

This part focuses on Text Functions — essential for cleaning, extracting, and combining text in Excel.

1️⃣ LEFT

Extracts characters from the beginning of a text.

=LEFT(A2,5)

Example:

INDIA123 → INDIA

2️⃣ RIGHT

Extracts characters from the end of a text.

=RIGHT(A2,3)

Example:

INV123 → 123

3️⃣ MID

Extracts characters from the middle of a text.

=MID(A2,4,5)

Example:

EMP-12345 → 12345

4️⃣ LEN

Counts the number of characters in a text.

=LEN(A2)

Example:

Excel → 5

Spaces are also counted.

5️⃣ TRIM

Removes unnecessary spaces from text.

=TRIM(A2)

Example:

"  John   Smith  " → "John Smith"

Very useful when cleaning imported data.

6️⃣ UPPER

Converts text to uppercase.

=UPPER(A2)

excel → EXCEL

7️⃣ LOWER

Converts text to lowercase.

=LOWER(A2)

EXCEL → excel

8️⃣ PROPER

Capitalizes the first letter of each word.

=PROPER(A2)

john smith → John Smith

9️⃣ CONCAT

Combines text from multiple cells.

=CONCAT(A2," ",B2)

Example:

A2 = John

B2 = Smith

Result → John Smith

🔟 TEXTJOIN

Combines multiple values using a delimiter.

=TEXTJOIN(", ",TRUE,A2:A5)

Example:

SQL, Excel, Power BI, Tableau

The TRUE tells Excel to ignore empty cells.

🧠 Quick Reference

LEFT → Extract from beginning

RIGHT → Extract from end

MID → Extract from middle

LEN → Count characters

TRIM → Remove extra spaces

UPPER → Convert to uppercase

LOWER → Convert to lowercase

PROPER → Capitalize words

CONCAT → Combine text

TEXTJOIN → Combine text with a separator

💡 Practice

Suppose:

A2 = "  john smith  "

Try creating formulas to:

1. Remove extra spaces → TRIM

2. Convert to uppercase → UPPER

3. Convert to lowercase → LOWER

4. Capitalize properly → PROPER

5. Count characters → LEN

❤️ Double Tap & React For Part 6!