Text Functions — Complete Guide (8 Topics)

Excel mein text data ko manipulate karna seekho — extract, combine, clean, format. LEFT se VALUE tak — real-world data cleaning examples ke saath Data Insights par.

📑 Is Part 4 Mein Aap Kya Sikhenge:

• Topic 1: LEFT, RIGHT, MID — Text Extract Karna
• Topic 2: LEN, FIND, SEARCH — Text Measure & Locate
• Topic 3: CONCATENATE / CONCAT / TEXTJOIN — Text Combine
• Topic 4: UPPER, LOWER, PROPER — Case Change
• Topic 5: TRIM, CLEAN — Spaces & Junk Remove
• Topic 6: SUBSTITUTE / REPLACE — Text Replace
• Topic 7: TEXT — Number to Formatted Text
• Topic 8: VALUE / NUMBERVALUE — Text to Number

📋 Note: Is Part 4 mein hum Employee Database ke saath ek naya Messy Data table bhi use karenge — real-world text cleaning practice ke liye.

🔍 Definition: LEFT extracts characters from the beginning (left side). RIGHT extracts from the end (right side). MID extracts from any position in the middle. These are the core text extraction functions in Excel.

🎯 Samjho Simple Bhasha Mein: Socho ek string hai "RAHUL SHARMA" — LEFT se "RAHUL" nikal sakte ho (left se 5 characters), RIGHT se "SHARMA" (right se 6), MID se beech ka koi bhi portion. Data cleaning mein bahut use hota hai — phone numbers se area code nikalna, employee codes split karna, dates extract karna.

💡 Syntax:

=LEFT(text, num_chars) → Left se characters
=RIGHT(text, num_chars) → Right se characters
=MID(text, start_position, num_chars) → Middle se

Note: Start position 1 se shuru hota hai (0 nahi).

💻 Real-World Examples:

// LEFT — Extract first 3 characters: =LEFT("RAHUL", 3) // "RAH" =LEFT(B2, 3) // First 3 chars of name
// RIGHT — Extract last 4 digits:
=RIGHT("9876543210", 4) // "3210"
=RIGHT(B2, 5) // Last 5 chars of name

// MID — Extract
from position 3, take 4 chars:
=MID("RAHUL SHARMA", 7, 6) // "SHARMA"
=MID("EMP-101-IT", 5, 3) // "101"

// Real Data: Employee Code "EMP-101-IT"
=LEFT("EMP-101-IT", 3) // "EMP" (prefix)
=MID("EMP-101-IT", 5, 3) // "101" (ID)
=RIGHT("EMP-101-IT", 2) // "IT" (dept)

// Dynamic extraction (with FIND):
// Extract first name
from "Rahul Sharma"
=LEFT(B2, FIND(" ", B2)-1)
// FIND(" ",B2) finds space at position 6
// LEFT takes 5 chars (6-1) = "Rahul"

// Extract last name:
=MID(B2, FIND(" ",B2)+1, 100)
//
From space+1 position, take 100 chars
// "Sharma" (100 is overkill but works!)

📊 Results Table:

⚠️ Common Mistakes:

• Mistake: MID mein start position 0 dena → Excel 1 se start karta hai, 0 se nahi!
  Fix: MID("Hello", 1, 3) = "Hel" — position 1 se shuru.
• Mistake: Fixed numbers use karna — data ka length change ho toh formula break.
  Fix: LEN aur FIND ke saath dynamic formulas banao (Topic 2).
• Mistake: Number pe LEFT/RIGHT use karna → Excel pehle number ko text mein convert karta hai, leading zeros hat sakte hain.
  Fix: TEXT function se pehle convert karo ya cell ko text format karo.

💬 Interview Questions:

🔍 Definition: LEN returns the length (number of characters) of text. FIND locates a substring within text (case-sensitive). SEARCH also locates substring but is case-insensitive and supports wildcards. These helper functions make LEFT/MID/RIGHT dynamic.

🎯 Samjho Simple Bhasha Mein: LEN matlab "kitne characters hain count karo" — "Rahul" ka LEN = 5. FIND matlab "yeh character kaunsi position pe hai" — "Rahul Sharma" mein space ki position = 6. SEARCH bhi same kaam karta hai but case ignore karta hai. Yeh sab LEFT/MID/RIGHT ke saath combine karke dynamic extraction hoti hai.

💡 Syntax:

=LEN(text) → Character count
=FIND(find_text, within_text, [start_num]) → Case-sensitive
=SEARCH(find_text, within_text, [start_num]) → Case-insensitive

FIND vs SEARCH:
FIND: Case-sensitive, no wildcards
SEARCH: Case-insensitive, supports * and ?

💻 Real-World Examples:

// LEN — Character count: =LEN("Rahul") // 5 =LEN("Rahul Sharma") // 12 (space bhi count!) =LEN(B2) // Name column ki length =LEN(" Hello ") // 7 (spaces count!)
// FIND — Locate character (case-sensitive):
=FIND(" ", "Rahul Sharma") // 6 (space at 6th)
=FIND("a", "Rahul") // 2 (lowercase a)
=FIND("A", "Rahul") // #VALUE! (case matters!)
=FIND("-", "EMP-101-IT") // 4 (first dash)
=FIND("-", "EMP-101-IT", 5) // 8 (second dash, start from 5)

// SEARCH — Case-insensitive:
=SEARCH("A", "Rahul") // 2 (finds both A and a)
=SEARCH("sharma", "Rahul Sharma") // 7 (case ignored!)

// SEARCH with Wildcards:
=SEARCH("R?h", "Rahul") // 1 (R + any char + h)

// Combined: Dynamic first name extraction
=LEFT(B2, FIND(" ", B2)-1)
// FIND space → LEFT takes before space

// Check if @ exists in email:
=IF(ISERROR(FIND("@", A1)), "Invalid", "Valid")

// Count words (count spaces + 1):
=LEN(A1) - LEN(SUBSTITUTE(A1, " ", "")) + 1
// "Rahul Kumar Sharma" → 18 - 16 + 1 = 3 words

📊 FIND vs SEARCH:

⚠️ Common Mistakes:

• Mistake: FIND case-sensitive hai — "a" aur "A" alag hain → #VALUE! error.
  Fix: Case ignore karna ho toh SEARCH use karo.
• Mistake: Character nahi mila toh FIND/SEARCH #VALUE! deta hai → Formula break.
  Fix: IFERROR wrap karo: =IFERROR(FIND("@", A1), 0)
• Mistake: LEN mein spaces count hona bhool jaana → " Hello " ka LEN = 7!
  Fix: TRIM pehle use karo: =LEN(TRIM(A1))

💬 Interview Questions:

🔍 Definition: CONCATENATE joins multiple text strings into one. CONCAT is its modern replacement (Excel 2019+). TEXTJOIN joins with a delimiter and can skip empty cells. The & operator is the simplest way to combine text.

🎯 Samjho Simple Bhasha Mein: Yeh sab "jodhne" ke kaam aate hain. First name aur last name milake full name banana, address ke parts jodhna, email ID banana — sab text joining hai. & operator sabse simple hai, TEXTJOIN sabse powerful. Real projects mein sab bahut use hote hain.

💡 Syntax:

=CONCATENATE(text1, text2, ...) → Old way
=CONCAT(text1, text2, ...) → Modern (2019+)
=TEXTJOIN(delimiter, ignore_empty, text1, ...) → Best
=A1 & " " & B1 → Simplest (& operator)

TEXTJOIN ka fayda: delimiter ek baar batao — sab ke beech lagega. ignore_empty=TRUE → blanks skip.

💻 Real-World Examples:

// & Operator — Simplest way: =B2 & " works in " & C2 & " department" // "Rahul works in IT department"
// Create email ID:
=LOWER(B2) & "." & LOWER(C2) & "@company.com"
// "rahul.it@company.com"

// CONCATENATE — Old but works everywhere:
=CONCATENATE(B2, " - ", C2, " - ", D2)
// "Rahul - IT - Delhi"

// CONCAT — Modern replacement:
=CONCAT(B2, " (", C2, ")")
// "Rahul (IT)"

// TEXTJOIN — Best for multiple
values:
=TEXTJOIN(", ", TRUE, B2, C2, D2)
// "Rahul, IT, Delhi"

=TEXTJOIN(" - ", TRUE, B2:D2)
// "Rahul - IT - Delhi" (range bhi le sakta hai!)

// TEXTJOIN with empty cell handling:
// A1="Rahul", A2="", A3="Sharma"
=TEXTJOIN(" ", TRUE, A1:A3)
// "Rahul Sharma" (empty cell skipped!)

=TEXTJOIN(" ", FALSE, A1:A3)
// "Rahul Sharma" (double space — empty included)

// Numbers ke saath text combine:
=B2 & " earns ₹" & TEXT(E2, "#,##0")
// "Rahul earns ₹75,000"

📊 Comparison:

⚠️ Common Mistakes:

• Mistake: Numbers combine karte time formatting lose hona → =A1&B1 jab A1=1000 → "1000" (no comma).
  Fix: TEXT function use karo: =TEXT(E2, "#,##0")
• Mistake: Space bhool jaana → =A1&B1 → "RahulSharma" bina space!
  Fix: =A1&" "&B1 ya TEXTJOIN use karo.
• Mistake: CONCATENATE mein range dena → CONCATENATE range accept nahi karta!
  Fix: TEXTJOIN ya CONCAT use karo — range accept karte hain.

💬 Interview Questions:

🔍 Definition: UPPER converts all text to UPPERCASE. LOWER converts to lowercase. PROPER capitalizes the first letter of each word (Title Case). Essential for data standardization and cleaning.

🎯 Samjho Simple Bhasha Mein: Data import karo toh names alag alag case mein aate hain — "RAHUL", "rahul", "rAhUl". Standardize karna zaroori hai. UPPER sab CAPS karta hai, LOWER sab small, PROPER har word ka pehla letter capital. Email IDs ke liye LOWER, names ke liye PROPER sabse zyada use hota hai.

💡 Syntax:

=UPPER(text) → "RAHUL SHARMA"
=LOWER(text) → "rahul sharma"
=PROPER(text) → "Rahul Sharma"

💻 Real-World Examples:

// Basic conversions: =UPPER("rahul sharma") 
// "RAHUL SHARMA" =LOWER("RAHUL SHARMA") 
// "rahul sharma" =PROPER("RAHUL SHARMA") 
// "Rahul Sharma" =PROPER("rAHUL sHARMA") 
// "Rahul Sharma"

// Real Use — Email generation (always lowercase):
=LOWER(B2) & "@company.com"

// "rahul@company.com"

// Employee ID (always uppercase):
=UPPER("emp-" & A2 & "-" & C2)

// "EMP-101-IT"

// Clean imported data:
=PROPER(TRIM(LOWER(B2)))

// " RAHUL SHARMA " → "Rahul Sharma" (clean!)

⚠️ Common Mistakes:

• Mistake: PROPER abbreviations pe galat kaam karta hai → "IT DEPT" → "It Dept"
  Fix: UPPER ya manual correction needed for abbreviations.
• Mistake: Original data replace nahi hona — formula alag column mein hota hai.
  Fix: Paste Special → Values use karo original column mein paste karne ke liye.

💬 Interview Questions:

🔍 Definition: TRIM removes all leading, trailing, and extra internal spaces from text — keeps only single spaces between words. CLEAN removes non-printable characters (ASCII 0-31) that come from web imports, database exports, or copy-paste from other systems.

🎯 Samjho Simple Bhasha Mein: Database ya web se data import karo toh extra spaces aur invisible characters aa jaate hain. " Rahul Sharma " mein leading/trailing spaces, double spaces — TRIM sab clean karta hai. CLEAN invisible junk characters hataata hai jo dikhte nahi but formulas break karte hain. Data cleaning ka pehla step hai TRIM+CLEAN.

💡 Syntax:

=TRIM(text) → Extra spaces remove
=CLEAN(text) → Non-printable chars remove
=TRIM(CLEAN(text)) → Both together — best practice!

💻 Real-World Examples:

// TRIM — Remove extra spaces: =TRIM(" Rahul Sharma ") // "Rahul Sharma" (clean!)
=TRIM(" Hello World ")
// "Hello World"

// CLEAN — Remove non-printable chars:
=CLEAN(A1)
// Removes hidden chars from imported data

// Best Practice — Both together:
=TRIM(CLEAN(A1))
// First CLEAN removes junk, then TRIM fixes spaces

// Full data cleaning pipeline:
=PROPER(TRIM(CLEAN(A1)))
// Clean + Trim + Proper Case = Perfect name!

// Check if TRIM needed:
=IF(LEN(A1)<>LEN(TRIM(A1)), "Has extra spaces", "Clean")
// Compares original vs trimmed length

// Remove specific non-breaking space (char 160):
=SUBSTITUTE(A1, CHAR(160), " ")
// TRIM doesn't remove char 160 — SUBSTITUTE does!

⚠️ Common Mistakes:

• Mistake: TRIM se non-breaking space (char 160) remove nahi hota → Web data mein common.
  Fix: =TRIM(SUBSTITUTE(A1, CHAR(160), " "))
• Mistake: VLOOKUP fail ho raha hai spaces ki wajah se → Extra spaces exact match nahi hone dete.
  Fix: Dono tables mein TRIM apply karo before VLOOKUP.
• Mistake: CLEAN se line breaks remove ho jaate hain → Kabhi kabhi line breaks chahiye hote hain.
  Fix: Selective cleaning ke liye SUBSTITUTE use karo.

💬 Interview Questions:

🔍 Definition: SUBSTITUTE replaces specific text with new text — works by matching text content. REPLACE replaces characters at a specific position — works by position number. Both are powerful for data transformation.

🎯 Samjho Simple Bhasha Mein: SUBSTITUTE content se kaam karta hai — "Delhi" dhundho "New Delhi" se replace karo. REPLACE position se kaam karta hai — 3rd character se 5 characters replace karo. SUBSTITUTE zyada common hai — Find & Replace jaisa but formula mein. REPLACE specific positions ke liye.

💡 Syntax:

=SUBSTITUTE(text, old_text, new_text, [instance_num])
=REPLACE(old_text, start_num, num_chars, new_text)

SUBSTITUTE: Text se dhundhta hai (multiple occurrences possible)
REPLACE: Position se dhundhta hai (exact location)

💻 Real-World Examples:

// SUBSTITUTE — Replace by content: =SUBSTITUTE("Hello World", "World", "Excel") // "Hello Excel"
=SUBSTITUTE(D2, "Delhi", "New Delhi")
// City column mein "Delhi" → "New Delhi"

// Replace specific occurrence (4th argument):
=SUBSTITUTE("a-b-c-d", "-", "|", 2)
// "a-b|c-d" (sirf 2nd dash replaced)

// Remove all spaces:
=SUBSTITUTE(A1, " ", "")
// "Rahul Sharma" → "RahulSharma"

// Remove all dashes from phone:
=SUBSTITUTE("98-765-43210", "-", "")
// "9876543210"

// REPLACE — Replace by position:
=REPLACE("ABCDEFGH", 3, 4, "XY")
// "ABXYGH" (position 3 se 4 chars replace)

// Mask phone number (last 4 visible):
=REPLACE("9876543210", 1, 6, "XXXXXX")
// "XXXXXX3210"

// Mask Aadhaar (show last 4 only):
=REPLACE("123456789012", 1, 8, "XXXX-XXXX-")
// "XXXX-XXXX-9012"

⚠️ Common Mistakes:

• Mistake: SUBSTITUTE case-sensitive hai → "Delhi" aur "delhi" alag treat karta hai.
  Fix: UPPER/LOWER se standardize karo pehle.
• Mistake: SUBSTITUTE aur REPLACE confuse karna → Content vs Position.
  Fix: Text dhundhna ho → SUBSTITUTE. Position se replace → REPLACE.
• Mistake: SUBSTITUTE sab occurrences replace karta hai by default.
  Fix: Specific occurrence ke liye 4th argument do: instance_num = 1, 2, 3...

💬 Interview Questions:

🔍 Definition: TEXT converts a number/date to a text string with a specified format. Essential when combining numbers with text using & operator — without TEXT, numbers lose their formatting (commas, currency, date format).

🎯 Samjho Simple Bhasha Mein: Jab numbers ko text ke saath combine karte ho, formatting gayab ho jaati hai. 75000 ko "₹75,000" dikhana ho aur "Salary is " ke saath jodhna ho — TEXT function se pehle format karo phir jodho. Dates ke saath bhi same — date number ko "15-Mar-2021" text mein convert karta hai.

💡 Syntax:

=TEXT(value, format_code)

Common Format Codes:
"#,##0" → 75,000
"₹#,##0" → ₹75,000
"0.00%" → 15.00%
"dd-mmm-yyyy" → 15-Mar-2021
"dddd" → Monday
"000" → 005 (leading zeros)

💻 Real-World Examples:

// Number formatting: =TEXT(75000, "#,##0") // "75,000" =TEXT(75000, "₹#,##0") // "₹75,000" =TEXT(0.15, "0.00%") // "15.00%" =TEXT(5, "000") // "005"
// Date formatting:
=TEXT(F2, "dd-mmm-yyyy") // "15-Mar-2021"
=TEXT(F2, "dddd") // "Monday"
=TEXT(F2, "mmmm yyyy") // "March 2021"
=TEXT(F2, "dd/mm/yyyy") // "15/03/2021"

// Combined with text (&):
=B2 & " earns " & TEXT(E2, "₹#,##0") & " per month"
// "Rahul earns ₹75,000 per month"

="Joined on " & TEXT(F2, "dddd, dd mmmm yyyy")
// "Joined on Monday, 15 March 2021"

// Without TEXT (problem!):
=B2 & " earns " & E2
// "Rahul earns 75000" (no comma, no ₹)

⚠️ Common Mistakes:

• Mistake: TEXT ka result number nahi text hai → Formulas mein math nahi hoga.
  Fix: TEXT sirf display ke liye hai — math ke liye original number use karo.
• Mistake: Format code galat likhna → Unexpected output.
  Fix: Format Cells mein Custom format try karo pehle, phir same code TEXT mein use karo.

💬 Interview Questions:

🔍 Definition: VALUE converts text that represents a number into an actual number. NUMBERVALUE is more flexible — handles different decimal separators and group separators from international formats. Essential when imported data has numbers stored as text.

🎯 Samjho Simple Bhasha Mein: Kabhi kabhi numbers text format mein aa jaate hain — cell left-aligned hoti hai, formulas kaam nahi karte. "75000" text hai — SUM nahi hoga! VALUE usse real number bana deta hai — phir SUM, AVERAGE sab kaam karta hai. Web aur database import mein yeh problem bahut common hai.

💡 Syntax:

=VALUE(text) → Text ko number banao
=NUMBERVALUE(text, [decimal_sep], [group_sep]) → International formats handle

Trick: =A1*1 ya =A1+0 ya =--A1 bhi text ko number bana deta hai!

💻 Real-World Examples:

// VALUE — Basic text to number: =VALUE("75000") 
// 75000 (number) =VALUE("3.14") 
// 3.14 =VALUE("15%") 
// 0.15 =VALUE("₹75,000") 
// #VALUE! error — has ₹ symbol!

// Fix currency text:
=VALUE(SUBSTITUTE(SUBSTITUTE(A1, "₹", ""), ",", ""))

// "₹75,000" → "75000" → 75000

// NUMBERVALUE — International formats:
=NUMBERVALUE("1.234,56", ",", ".")

// European format → 1234.56

// Quick tricks (text to number):
=A1 * 1 
// Multiply by 1
=A1 + 0 
// Add 0
=--A1 
// Double negative

// All three convert text "75000" to number 75000

// Check if cell is number or text:
=ISNUMBER(A1) 
// TRUE if number, FALSE if text
=ISTEXT(A1) 
// TRUE if text, FALSE if number

// Date text to actual date:
=DATEVALUE("15-Mar-2021")

// Converts date text to date serial number

// Full cleanup pipeline:
=VALUE(TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A1, "₹", ""), ",", ""))))

// Handles: " ₹ 75,000 " → 75000

📊 Text vs Number — How to Check:

⚠️ Common Mistakes:

• Mistake: VALUE mein currency symbol ya comma dena → #VALUE! error.
  Fix: Pehle SUBSTITUTE se symbols remove karo, phir VALUE apply.
• Mistake: Text numbers pe SUM karna → 0 result aata hai!
  Fix: Pehle VALUE se convert karo, ya SUM ke bajaye SUMPRODUCT use karo.
• Mistake: NUMBERVALUE ki availability — Excel 2013+ mein hai.
  Fix: Older versions mein VALUE + SUBSTITUTE combo use karo.

💬 Interview Questions:

🎯 Data Cleaning Pipeline — Yaad Kar Lo!

Step 1: =CLEAN(A1) — Remove invisible characters

Step 2: =TRIM() — Remove extra spaces

Step 3: =PROPER() — Fix case (for names)

Step 4: =SUBSTITUTE() — Replace wrong values

Step 5: =VALUE() — Convert text-numbers to real numbers

Combined: =PROPER(TRIM(CLEAN(A1))) — All-in-one name cleanup!

Next: Data Insights Excel Masterclass — Part 5

Part 5 mein hum cover karenge: Date & Time Functions — TODAY, NOW, DATE, YEAR, MONTH, DAY, DATEDIF, WEEKDAY, EOMONTH, NETWORKDAYS, WORKDAY — Excel ka date mastery Data Insights par.

Happy Learning & Keep Excelling! 🚀