Characters extraction from start, end, or middle (LEFT, RIGHT, MID), counting total characters including spaces (LEN), and removing trailing/double spaces with TRIM.
This lesson is a free preview. No account is needed to read it.
The video for this lesson has not been published yet. The written lesson below is the full material for this session.
Text Functions ka use raw corporate data ko clean, extract, aur standardize karne ke liye hota hai.
LEFT text ke left side se specified characters extract karta hai.
=LEFT(text, [num_chars])
=LEFT("EXCEL2024", 5) → EXCEL=LEFT("PRD-4589", 3) → PRDnum_chars omit karein to default 1 character return hota hai.[!NOTE] Agar
num_charstext ki total length se zyada ho, to Excel poora text return karta hai. Negative number dene par#VALUE!error aata hai.
RIGHT text ke right side se characters extract karta hai.
=RIGHT(text, [num_chars])
=RIGHT("EXCEL2024", 4) → 2024=RIGHT("INV-987654", 6) → 987654MID text ke kisi specified starting position se characters extract karta hai.
=MID(text, start_num, num_chars)
=MID("EXCEL2024", 2, 3) → XCE=MID("EMP-2026-105", 5, 4) → 2026[!IMPORTANT] Excel mein character indexing hamesha 1 se shuru hoti hai, 0 se nahi!
Text mein total characters count karta hai (spaces aur symbols included).
=LEN("Excel") --> 5
=LEN("Excel 2024") --> 10
Practical Rule (Length Validation):
=IF(LEN(A2)=10, "Valid Mobile Number", "Check Digits")
Text ke leading, trailing extra spaces remove karta hai aur words ke beech multiple spaces ko single space mein convert karta hai.
=TRIM(" Aman Kumar ") --> "Aman Kumar"
[!TIP] TRIM ASCII code 32 (ordinary space) ko clean karta hai. Web ya ERP se imported non-breaking space (code 160) ko clean karne ke liye:
=TRIM(SUBSTITUTE(A2, CHAR(160), " "))
INV-2026-99 likhein aur prefix INV alag karein =LEFT(A2, 3).2026 nikaalein =MID(A2, 5, 4).99 nikaalein =RIGHT(A2, 2).=TRIM(A2) se clean karein.Practice live Excel formulas directly in your browser. Real calculation verified by HyperFormula.
Calculate total revenue in cell D6 by summing all items in column D (cells D2 to D5).
Every formula you enter is evaluated by a real spreadsheet engine in your browser — results are not pre-computed.
| # | A | B | C | D |
|---|---|---|---|---|
| 1 | Product | Quantity | Unit Price | Total |
| 2 | Laptop | 5 | 45000 | 225000 |
| 3 | Mouse | 20 | 500 | 10000 |
| 4 | Keyboard | 15 | 1200 | 18000 |
| 5 | Monitor | 8 | 12500 | 100000 |
| 6 | Grand Total |
5 practical questions testing your mastery of Excel text manipulation, string cleaning, and extraction formulas.
This quiz has 5 questions. Sign in to answer them — results are saved to your account.
Sign in to attemptSign in to submit your work for this assignment.
Stuck on a formula or edge case? Ask questions, upvote solutions, and earn community XP.
No questions yet on this lesson
Be the first to ask a doubt or start a discussion with peers and mentors!
Module 8: Mathematical & Statistical Functions
Module 9: Conditional Functions & Criteria-Based Analysis