Tutorials›Excel›Text Functions
🟢 Free Demo
Excel TutorialTopic 7 of 31

Text Functions

Clean, split, combine, and transform text data using Excel built-in text functions.

✅ What You Will Learn

›How to extract parts of a text string with LEFT, RIGHT, and MID
›How to combine text with CONCATENATE, CONCAT, and TEXTJOIN
›How to clean data with TRIM, CLEAN, UPPER, LOWER, and PROPER
›How to find and replace characters using FIND, SUBSTITUTE, and REPLACE
›How to convert between text and numbers with TEXT and VALUE

Real-world data is rarely clean. Names have inconsistent capitalisation, product codes have extra spaces, dates are stored as text, and customer IDs need parts extracted. Text functions are what analysts use to clean and reshape this messy data before analysis.

Excel has over 25 text functions. The most useful ones fall into four categories: extraction (LEFT, MID, RIGHT), combination (CONCAT, TEXTJOIN), cleaning (TRIM, PROPER, UPPER, LOWER), and transformation (SUBSTITUTE, TEXT, VALUE). Mastering these eliminates hours of manual data cleanup.

In modern Excel (Microsoft 365), many text operations can also be done with TEXTSPLIT and TEXTBEFORE/TEXTAFTER — powerful additions for splitting strings without formulas.

📋 Raw customer data needing text cleanup

Raw NameEmailPhoneProduct CodeDate (text)
rahul sharma rahul@email.com9876543210PROD-2024-LPT-0115-01-2026
PRIYA VERMApriya@email.com9988776655PROD-2024-MOB-0722-02-2026
amit kumaramit@email.com9123456789PROD-2025-TAB-0305-03-2026

Syntax

EXCEL SYNTAX
=LEFT(text, num_chars)        → first N characters
=RIGHT(text, num_chars)       → last N characters
=MID(text, start_num, num_chars) → characters from the middle
=LEN(text)                    → total character count
=TRIM(text)                   → remove extra spaces
=UPPER/LOWER/PROPER(text)     → change case
=CONCATENATE(text1, text2)    → join text (old syntax)
=CONCAT(text1, text2, ...)    → join text (modern)
=TEXTJOIN(delimiter, ignore_empty, text1, text2, ...)
=SUBSTITUTE(text, old, new)   → replace all occurrences
=TEXT(value, format_code)     → convert number to formatted text

Examples

Example 1TRIM and PROPER — clean names with extra spaces and wrong case
=PROPER(TRIM(A2))
OUTPUT
"Rahul Sharma"  (spaces removed, correct capitalisation)
💡

Functions nest inside each other — TRIM runs first on A2, then PROPER applies to the cleaned result. Always nest from the inside out.

Example 2LEFT and FIND — extract everything before the @ in an email
=LEFT(B2, FIND("@", B2) - 1)
OUTPUT
"rahul"
💡

FIND returns the position of "@". Subtracting 1 gives the number of characters before it. LEFT then extracts exactly that many characters.

Example 3TEXTJOIN — build a full address from separate columns
=TEXTJOIN(", ", TRUE, D2, E2, F2, G2)
OUTPUT
"Sector 51, Noida, Uttar Pradesh, 201301"
💡

The second argument TRUE means empty cells are skipped — no ",, " gaps if any field is blank. TEXTJOIN is far better than chaining & operators.

📌 Key Points to Remember

  • ✓TRIM removes leading, trailing, and duplicate internal spaces — use it on every text column imported from external systems
  • ✓PROPER capitalises the first letter of each word — good for names, but check for exceptions like "McDonald"
  • ✓FIND is case-sensitive; SEARCH is not — use SEARCH when you do not know the case
  • ✓TEXT(value, format) converts numbers to text for display: =TEXT(TODAY(),"DD-MMM-YYYY") → "20-Aug-2026"
  • ✓VALUE(text) converts a text-stored number back to a real number so SUM and other math works on it

🏢 Real-World Application

Data exported from CRM systems, ERPs, and banking portals regularly has issues like names in all caps, product codes with inconsistent separators, and dates stored as text. Analysts at companies like Flipkart and Amazon India spend significant time cleaning data with these functions before dashboards can be built. A TRIM+PROPER cleanup pass on a 10,000-row file takes seconds with formulas.

⚠️ Common Mistakes to Avoid

WRONGUsing CONCATENATE instead of & or CONCAT for simple joins
FIX="Hello "&A1&"!" is cleaner and shorter than =CONCATENATE("Hello ",A1,"!"). Both work, but & is the modern standard for short joins.
WRONGForgetting that TRIM only removes space characters (ASCII 32), not non-breaking spaces from web data
FIXIf TRIM does not remove a space, it may be a non-breaking space (ASCII 160). Use =SUBSTITUTE(TRIM(A1),CHAR(160)," ") to catch both types.
WRONGLeaving numbers stored as text and wondering why SUM returns 0
FIXUse =VALUE(A1) to convert, or select the column, click the yellow warning triangle, and choose "Convert to Number".
✏️Test Yourself

What does =MID("PROD-2024-LPT", 6, 4) return?

❓ Frequently Asked Questions

How do I split a full name into first and last name columns?

Use =LEFT(A1,FIND(" ",A1)-1) for the first name and =MID(A1,FIND(" ",A1)+1,LEN(A1)) for the last name. In Excel 365, Flash Fill (Ctrl+E) often handles this automatically.

What is the difference between SUBSTITUTE and REPLACE?

SUBSTITUTE replaces a specific text string wherever it appears. REPLACE replaces characters at a specific position regardless of what they are. Use SUBSTITUTE for find-and-replace logic, REPLACE for positional manipulation.

How do I format a number as currency text?

=TEXT(A1,"₹#,##0") converts 52000 to "₹52,000". Note that the result is text — you cannot do arithmetic on it. Use TEXT only for display purposes.

✏️ Practice Exercise

You have a column of product codes in the format "CAT-YEAR-SKU" (e.g. "ELEC-2024-LPT01"). Write formulas to extract: (1) the category (before the first dash), (2) the year (between the two dashes), (3) the SKU (after the second dash). Then write a TEXTJOIN formula that rebuilds the code from those three parts with "/" as the separator instead.

← PreviousSUMIF & COUNTIFNext →Date Functions
🎓 Level Up Faster

Learn Excel with Live Trainer Guidance

These tutorials give you the foundations. Our live Excel course at EVIKA Academy, Noida teaches you to build real dashboards on actual business data — with a trainer who uses Excel professionally every day.