LEFT vs MID vs RIGHT — Text Extraction ✂️

Excel ke 3 essential text extraction functions — LEFT starting characters nikaalta hai, MID beech ke characters, RIGHT ending characters. Kaunsa kab use karo, syntax, aur real-world scenarios. Employee data ke examples, common mistakes aur interview questions ke saath. Data Insights par.

📑 Is Blog Mein Kya Sikhenge:

• 🟢 Basic: Text Extraction kya hai, kyu zaroori hai
• 🟡 Medium: LEFT Function — Starting characters extract
• 🟡 Medium: MID Function — Middle characters extract
• 🟡 Medium: RIGHT Function — Ending characters extract
• 🔴 Advanced: Combined use with FIND, LEN, SEARCH
• 📋 Comparison: LEFT vs MID vs RIGHT — differences aur use cases
• 💬 Interview: Top asked questions

📘 Definition: Text Extraction means EXTRACTING specific portions from a text string. Excel provides 3 core functions: LEFT extracts characters from the START, MID from the MIDDLE (any position), RIGHT from the END. These functions are ESSENTIAL for data cleaning, parsing IDs, extracting parts of codes, splitting names, and formatting text data. Available in ALL Excel versions.

🎯 Samjho Hinglish Mein: Employee ID "EMP-101-IT-2024" mein alag-alag parts hain — "EMP" (prefix), "101" (number), "IT" (dept), "2024" (year). Ye parts nikaalne ke liye text extraction functions use karte hain. LEFT se "EMP" nikaal sakte ho (first 3 chars), MID se "101" ya "IT" (middle portions), RIGHT se "2024" (last 4 chars). Real-world mein bahut kaam aata hai — codes parse karna, names split karna, dates extract karna. Interview mein basic text manipulation must-know!

📋 Quick Overview:

📘 Definition: LEFT function extracts a specified number of characters from the START (left side) of a text string. Give it text and character count — returns first N characters. Simple, fast, works in ALL Excel versions. Perfect for extracting prefixes, first initials, area codes, country codes.

📊 Sample Data (Employee Table):

💡 Syntax:

=LEFT(text, [num_chars])

# Parameters:
# text       = string to extract from (cell reference or text)
# num_chars  = number of characters to extract (default = 1)
# Returns starting characters from left side

💻 Formula Examples:

# Example 1: Extract prefix "EMP" (first 3 chars)
=LEFT(A2, 3)
# Result: EMP
# A2 = "EMP-101-IT-2024" → first 3 chars = "EMP"

# Example 2: First name from full name
=LEFT(B2, 5)
# Result: Aarav (first 5 chars of "Aarav Sharma")

# Example 3: First character (initial)
=LEFT(B2, 1)
# Result: A (first character only)

# Example 4: Country code from phone
=LEFT(D2, 3)
# Result: +91 (country code)

# Example 5: Username from email (before @)
=LEFT(C2, FIND("@", C2) - 1)
# Result: aarav (before "@")
# FIND locates @ position, LEFT extracts before it

# Example 6: Dynamic first name (before space)
=LEFT(B2, FIND(" ", B2) - 1)
# Result: Aarav (works for any name length!)
# Finds space, extracts everything before

# Example 7: Default num_chars (returns 1)
=LEFT(A2)
# Result: E (single character, default)

📊 Expected Output:

📘 Definition: MID function extracts characters from ANY POSITION in a text string. Give it text, starting position, and number of characters — returns substring. Most flexible of the three — can extract from beginning, middle, or end. Perfect for parsing structured codes, extracting date parts, getting middle portions.

💡 Syntax:

=MID(text, start_num, num_chars)

# Parameters:
# text       = string to extract from
# start_num  = position to start (1 = first char)
# num_chars  = how many characters to extract
# All parameters MANDATORY (unlike LEFT/RIGHT)

💻 Formula Examples:

# Sample data: A2 = "EMP-101-IT-2024"
# Position:     1234567890123456

# Example 1: Extract "101" (position 5, 3 chars)
=MID(A2, 5, 3)
# Result: 101 (employee ID number)

# Example 2: Extract "IT" (position 9, 2 chars)
=MID(A2, 9, 2)
# Result: IT (department code)

# Example 3: Extract "2024" (position 12, 4 chars)
=MID(A2, 12, 4)
# Result: 2024 (year)

# Example 4: MID can work like LEFT (start at 1)
=MID(A2, 1, 3)
# Result: EMP (same as =LEFT(A2, 3))

# Example 5: Middle name from full name
# If B2 = "Aarav Kumar Sharma" (with middle name)
=MID(B2, 7, 5)
# Result: Kumar (middle name)

# Example 6: Dynamic — extract dept between dashes
=MID(A2, 
     FIND("-", A2, FIND("-", A2)+1)+1, 2)
# Result: IT (finds second dash, extracts 2 chars after)

# Example 7: Domain from email (after @)
=MID(C2, 
     FIND("@", C2)+1, 
     100)
# Result: company.com (everything after @)

# Example 8: Phone number without country code
=MID(D2, 5, 10)
# Result: 9876543210 (skip "+91-", get 10 digits)

📊 Expected Output:

📘 Definition: RIGHT function extracts a specified number of characters from the END (right side) of a text string. Give it text and character count — returns last N characters. Perfect for extracting file extensions, year from dates, last digits of IDs, suffixes, last names.

💡 Syntax:

=RIGHT(text, [num_chars])

# Parameters:
# text       = string to extract from
# num_chars  = number of characters (default = 1)
# Counts from END (right side)

💻 Formula Examples:

# Example 1: Extract year "2024" (last 4 chars)
=RIGHT(A2, 4)
# Result: 2024 (from "EMP-101-IT-2024")

# Example 2: Last name from full name
=RIGHT(B2, 6)
# Result: Sharma (from "Aarav Sharma")

# Example 3: Last digit
=RIGHT(D2, 1)
# Result: 0 (last digit of phone)

# Example 4: File extension
# If cell = "report.xlsx"
=RIGHT(A5, 4)
# Result: xlsx (last 4 chars)

# Example 5: Domain extension from email
=RIGHT(C2, 3)
# Result: com (last 3 chars of email)

# Example 6: Dynamic last name (after last space)
=RIGHT(B2, 
      LEN(B2) - FIND(" ", B2))
# Result: Sharma (works for any name length!)
# Total length minus space position = last part length

# Example 7: Last 10 digits of phone (mobile number)
=RIGHT(D2, 10)
# Result: 9876543210 (drops "+91-" prefix)

# Example 8: Default num_chars (returns 1)
=RIGHT(A2)
# Result: 4 (single character, default)

📊 Expected Output:

📘 Definition: LEFT, MID, RIGHT ka real power aata hai jab combine hote hain FIND (position finder — case-sensitive), SEARCH (position finder — case-insensitive), aur LEN (text length) ke saath. Yeh combinations dynamic extraction possible banate hain — hardcoded positions ki jagah automatic position detection.

📋 Helper Functions Overview:

💻 Powerful Combinations:

# Sample: B2 = "Aarav Sharma", C2 = "aarav@company.com"

# PATTERN 1: LEFT + FIND — Extract before delimiter

# First name (before space)
=LEFT(B2, FIND(" ", B2) - 1)
# Result: Aarav (dynamic length!)

# Username (before @)
=LEFT(C2, FIND("@", C2) - 1)
# Result: aarav


# PATTERN 2: RIGHT + LEN + FIND — Extract after delimiter

# Last name (after space)
=RIGHT(B2, LEN(B2) - FIND(" ", B2))
# Result: Sharma

# Domain (after @)
=RIGHT(C2, LEN(C2) - FIND("@", C2))
# Result: company.com


# PATTERN 3: MID + FIND — Extract between two delimiters

# For A2 = "EMP-101-IT-2024"
# Extract "101" (between first and second dash)
=MID(A2, 
     FIND("-", A2) + 1,
     FIND("-", A2, FIND("-", A2) + 1) 
     - FIND("-", A2) - 1)
# Result: 101


# PATTERN 4: MID for domain from email
=MID(C2, 
     FIND("@", C2) + 1, 
     FIND(".", C2) - FIND("@", C2) - 1)
# Result: company (between @ and .)

💻 Real-World Extraction Patterns:

# SPLIT FULL NAME into First and Last

# First Name
=LEFT(B2, FIND(" ", B2) - 1)

# Last Name
=RIGHT(B2, LEN(B2) - FIND(" ", B2))


# SPLIT EMAIL into Username and Domain

# Username (before @)
=LEFT(C2, FIND("@", C2) - 1)

# Domain (after @)
=MID(C2, FIND("@", C2) + 1, 100)


# PARSE EMPLOYEE CODE "EMP-101-IT-2024"

# Prefix (first 3 chars)
=LEFT(A2, 3)                    # EMP

# ID number (after first dash, before second)
=MID(A2, 5, 3)                  # 101

# Department (position 9-10)
=MID(A2, 9, 2)                  # IT

# Year (last 4 chars)
=RIGHT(A2, 4)                   # 2024


# EXTRACT AREA CODE + NUMBER from phone
# D2 = "+91-9876543210"

# Country code
=LEFT(D2, 3)                    # +91

# Phone number
=RIGHT(D2, 10)                  # 9876543210

📘 Definition: Yeh section 3 functions ki DIRECT COMPARISON dikhata hai — same text pe different results. Understanding differences se right function choose karna asaan ho jaayega.

💻 Same Text — 3 Different Extractions:

# TEXT: "EMP-101-IT-2024"
# Positions: 12345678901234567

# LEFT — extract from START
=LEFT(A2, 3)              # EMP (first 3)
=LEFT(A2, 7)              # EMP-101 (first 7)

# MID — extract from ANY position
=MID(A2, 5, 3)            # 101 (position 5, 3 chars)
=MID(A2, 9, 2)            # IT (position 9, 2 chars)

# RIGHT — extract from END
=RIGHT(A2, 4)             # 2024 (last 4)
=RIGHT(A2, 7)             # IT-2024 (last 7)


# Same result — 3 different approaches
# Task: Extract "EMP" (first 3 chars)

=LEFT(A2, 3)              # Best — direct
=MID(A2, 1, 3)            # Works — but overkill
# RIGHT cannot do this — start from end

📋 Complete Comparison Table:

📋 Decision Guide:

💻 Real-World Scenarios:

# Scenario 1: HR — extract employee ID number
=MID(EmpCode, 5, 3)
# From "EMP-101-IT-2024" → "101"

# Scenario 2: Marketing — first names for personalization
=LEFT(FullName, FIND(" ", FullName) - 1)
# "Hi Aarav," instead of "Hi Aarav Sharma,"

# Scenario 3: Finance — extract year from transaction code
=RIGHT(TransCode, 4)
# From "TXN-2024" → "2024"

# Scenario 4: Data cleaning — email domain analysis
=MID(Email, FIND("@", Email) + 1, 100)
# Count Gmail vs Yahoo vs Company emails

# Scenario 5: Report generation — format phone numbers
=LEFT(Phone, 3) & "-" & MID(Phone, 5, 4) & "-" & RIGHT(Phone, 6)
# Result: +91-9876-543210

# Scenario 6: Product codes — split into components
# "PRD-A123-XL-RED"
=LEFT(A2, 3)         # PRD (prefix)
=MID(A2, 5, 4)       # A123 (SKU)
=MID(A2, 10, 2)      # XL (size)
=RIGHT(A2, 3)        # RED (color)

# Scenario 7: License plate parsing
# "DL-01-AB-1234"
=LEFT(A2, 2)         # DL (state)
=MID(A2, 4, 2)       # 01 (district)
=RIGHT(A2, 4)        # 1234 (number)

# Scenario 8: Date string parsing
# "2024-01-15"
=LEFT(A2, 4)         # 2024 (year)
=MID(A2, 6, 2)       # 01 (month)
=RIGHT(A2, 2)        # 15 (day)

💻 Mistakes vs Correct Code:

# ❌ MISTAKE 1: Hardcoded length for first name
=LEFT(B2, 5)
# Works for "Aarav Sharma" (Aarav = 5 chars)
# But "Ishita Verma" → "Ishit" (wrong!)

# ✅ FIX: Dynamic — use FIND to locate space
=LEFT(B2, FIND(" ", B2) - 1)
# Works for ANY name length

# ❌ MISTAKE 2: MID start position 0
=MID(A2, 0, 3)
# Returns #VALUE! error — position starts at 1

# ✅ FIX: Position starts from 1
=MID(A2, 1, 3)
# Works — extracts first 3 chars

# ❌ MISTAKE 3: Number treated as text
=RIGHT(A2, 4) + 1
# If A2 = "EMP-2024", result = "20241" (concatenation!)

# ✅ FIX: Convert to number with VALUE()
=VALUE(RIGHT(A2, 4)) + 1
# Result: 2025 (actual math)

# ❌ MISTAKE 4: Trailing spaces in RIGHT
=RIGHT(B2, 1)
# If B2 = "Aarav " (with space) → returns " " (space)

# ✅ FIX: TRIM first, then RIGHT
=RIGHT(TRIM(B2), 1)
# Removes trailing space, gets actual last char

# ❌ MISTAKE 5: Wrong MID position for structured code
# "EMP-101-IT" — extract "IT" (department)
=MID(A2, 8, 2)
# Wrong! Position 8 is "-", not "I"

# ✅ FIX: Count positions carefully
# E-M-P---1-0-1---I-T
# 1-2-3-4-5-6-7-8-9-10
=MID(A2, 9, 2)
# Position 9 = "I", 2 chars = "IT"

# ══════════════════════════════════════
# LEFT — Start of text
# ══════════════════════════════════════
=LEFT(text, [num_chars])

# Fixed length
=LEFT(A2, 3)                # First 3 chars

# Dynamic (before delimiter)
=LEFT(A2, FIND(" ", A2) - 1)


# ══════════════════════════════════════
# MID — Middle of text (any position)
# ══════════════════════════════════════
=MID(text, start_num, num_chars)

# Fixed position
=MID(A2, 5, 3)             # Position 5, 3 chars

# Between two delimiters
=MID(A2, 
     FIND("@", A2) + 1,
     100)                # Everything after @


# ══════════════════════════════════════
# RIGHT — End of text
# ══════════════════════════════════════
=RIGHT(text, [num_chars])

# Fixed length
=RIGHT(A2, 4)               # Last 4 chars

# Dynamic (after delimiter)
=RIGHT(A2, LEN(A2) - FIND(" ", A2))


# ══════════════════════════════════════
# HELPER Functions
# ══════════════════════════════════════

=FIND("@", A2)              # Position of @ (case-sensitive)
=SEARCH("@", A2)            # Position (case-insensitive, wildcards)
=LEN(A2)                     # Total character count
=TRIM(A2)                    # Remove extra spaces


# ══════════════════════════════════════
# COMMON PATTERNS
# ══════════════════════════════════════

# Split full name (First / Last)
=LEFT(B2, FIND(" ", B2) - 1)     # First name
=RIGHT(B2, LEN(B2) - FIND(" ", B2))  # Last name

# Split email (Username / Domain)
=LEFT(C2, FIND("@", C2) - 1)     # Username
=MID(C2, FIND("@", C2) + 1, 100) # Domain

# Parse structured code "EMP-101-IT-2024"
=LEFT(A2, 3)                # EMP (prefix)
=MID(A2, 5, 3)             # 101 (ID)
=MID(A2, 9, 2)             # IT (dept)
=RIGHT(A2, 4)               # 2024 (year)

# Convert extracted number to real number
=VALUE(RIGHT(A2, 4))       # Number, not text


# ══════════════════════════════════════
# MODERN ALTERNATIVES (Excel 365)
# ══════════════════════════════════════

=TEXTBEFORE(A2, " ")         # Same as LEFT+FIND
=TEXTAFTER(A2, " ")          # Same as RIGHT+LEN+FIND
=TEXTSPLIT(A2, " ")          # Split into array


# ══════════════════════════════════════
# GOLDEN RULES
# ══════════════════════════════════════
# 1. LEFT = start, MID = anywhere, RIGHT = end
# 2. Position counting starts at 1 (not 0)
# 3. Numbers extracted as TEXT — use VALUE() for math
# 4. Trailing spaces cause bugs — use TRIM
# 5. Dynamic extraction: combine with FIND/SEARCH
# 6. FIND = case-sensitive, SEARCH = case-insensitive
# 7. Modern Excel: try TEXTBEFORE/AFTER/SPLIT

Next: Data Insights Excel Topic Wise

Agle blog mein hum cover karenge: XLOOKUP vs INDEX & MATCH — Modern vs Classic Lookup. Excel ka final comparison blog — kaunsa lookup method modern, kaunsa classic, aur kab kya use karo. Real examples, performance comparison, aur interview questions ke saath. Excel Topic Wise series ka last blog — Data Insights par!

Happy Learning & Keep Exploring! 🚀