Complete Guide to Text Functions: The Secret to Handling Text in Reports

When preparing reports, you often need to organize data as text, not just numbers. By using Excel text functions effectively, you can neatly organize names, codes, email addresses, and more.

✅ LEFT / RIGHT – Extract Part of a Text String

Example: Extract the last name from a name

=LEFT(A2, 1)

Example: Extract the last four digits of a phone number

=RIGHT(B2, 4)

✅ MID – Extract the Middle of a Text String

Example: Extract the date of birth from an identification number

=MID(C2, 1, 6)

✅ LEN – Check Text String Length

Example: Check the number of digits in an employee ID

=LEN(D2)

✅ FIND / SEARCH – Find the Position of a Specific Character

Example: Find the position of @ in an email address

=FIND("@", E2)

✅ SUBSTITUTE – Replace Characters

Example: Remove hyphens (-) from a phone number

=SUBSTITUTE(F2, "-", "")

✅ Practical Example 1: Split First and Last Names

=LEFT(G2, FIND(" ", G2)-1)

=RIGHT(G2, LEN(G2)-FIND(" ", G2))

These formulas split the first and last names based on the space.

✅ Practical Example 2: Extract a Department Code

Check the department code using the first three digits of an employee ID.

=LEFT(H2, 3)

✅ Practical Example 3: Extract an Email Domain

=MID(I2, FIND("@", I2)+1, LEN(I2))

This extracts only the domain from an email address.

📌 Summary

• LEFT / RIGHT: Extract text from either end of a string
• MID: Extract the middle portion
• LEN: Check the length
• FIND / SEARCH: Find a position
• SUBSTITUTE: Replace characters

🙋 Frequently Asked Questions (FAQ)

Text functions are more than tools for handling text— they are a secret weapon for creating polished business reports 🚀