LEFT, RIGHT, MID: Text Extraction Guide (2025)
Quick Answer: Extract text: LEFT(text,num) gets first N chars. RIGHT(text,num) gets last N. MID(text,start,num) gets middle. Example: =LEFT("Excel",2) returns "Ex". =MID("Excel",2,3) returns "xce".
What are LEFT, RIGHT, and MID?
LEFT, RIGHT, and MID are Excel's text extraction functions. They pull specific characters from a text string based on position - from the beginning, end, or middle.
LEFT
=LEFT("Excel", 2)Returns: "Ex"
First 2 characters from left
RIGHT
=RIGHT("Excel", 2)Returns: "el"
Last 2 characters from right
MID
=MID("Excel", 2, 3)Returns: "xce"
3 chars starting at position 2
Visual Example:
Text: "EXCEL2025"
LEFT("EXCEL2025", 5) → "EXCEL"
RIGHT("EXCEL2025", 4) → "2025"
MID("EXCEL2025", 6, 4) → "2025"
💡 Common Use Cases
- • Parse names: Extract first/last names from full names
- • Product codes: Split SKU codes into category and ID
- • Dates: Extract year, month, day from date strings
- • Phone numbers: Separate area codes from numbers
- • Email addresses: Extract username or domain
- • File paths: Get file extensions or folder names
Syntax Guide
1. LEFT Function
=LEFT(text, [num_chars])
text (required)
The text string to extract from
A1 or "Hello World"num_chars (optional)
Number of characters to extract (default: 1)
5 extracts 5 charactersExamples:
=LEFT("Microsoft", 5) → "Micro"
=LEFT(A1) → First character of A1
2. RIGHT Function
=RIGHT(text, [num_chars])
text (required)
The text string to extract from
B1 or "Document.pdf"num_chars (optional)
Number of characters from the end (default: 1)
3 extracts last 3 charactersExamples:
=RIGHT("Document.pdf", 3) → "pdf"
=RIGHT(B1, 4) → Last 4 characters of B1
3. MID Function
=MID(text, start_num, num_chars)
text (required)
The text string to extract from
C1 or "ABC-123-XYZ"start_num (required)
Starting position (1 = first character)
5 starts at 5th characternum_chars (required)
Number of characters to extract
3 extracts 3 charactersExamples:
=MID("ABC-123-XYZ", 5, 3) → "123"
=MID(C1, 2, 4) → 4 chars starting at position 2
⚠️ Important Notes
- • Position counting: Excel starts at 1, not 0 (first character is position 1)
- • Too many characters: If you request more than available, Excel returns what exists (no error)
- • Negative numbers: Returns #VALUE! error
- • Start beyond length: MID returns empty string if start_num > text length
- • Case sensitive: These functions preserve original case
15+ Practical Examples
LEFT Examples
1. Extract First Name
=LEFT(A1, FIND(" ", A1)-1)Input: "John Smith" → Output: "John"
2. Extract Area Code
=LEFT(A1, 3)
Input: "555-1234" → Output: "555"
3. Get Product Category from SKU
=LEFT(A1, 3)
Input: "ELC-12345" → Output: "ELC"
4. Extract Username from Email
=LEFT(A1, FIND("@", A1)-1)Input: "user@domain.com" → Output: "user"
5. Extract Year from Date String
=LEFT(A1, 4)
Input: "2025-12-31" → Output: "2025"
RIGHT Examples
6. Extract File Extension
=RIGHT(A1, LEN(A1)-FIND(".", A1))Input: "document.pdf" → Output: "pdf"
7. Get Last 4 Digits of Credit Card
=RIGHT(A1, 4)
Input: "1234-5678-9012-3456" → Output: "3456"
8. Extract Last Name
=RIGHT(A1, LEN(A1)-FIND(" ", A1))Input: "John Smith" → Output: "Smith"
9. Extract Domain from Email
=RIGHT(A1, LEN(A1)-FIND("@", A1))Input: "user@domain.com" → Output: "domain.com"
10. Get Day from Date String
=RIGHT(A1, 2)
Input: "2025-12-31" → Output: "31"
MID Examples
11. Extract Middle Name
=MID(A1, FIND(" ", A1)+1, FIND(" ", A1, FIND(" ", A1)+1)-FIND(" ", A1)-1)Input: "John Michael Smith" → Output: "Michael"
12. Extract Product ID from SKU
=MID(A1, 5, 5)
Input: "ELC-12345-AA" → Output: "12345"
13. Extract Month from Date String
=MID(A1, 6, 2)
Input: "2025-12-31" → Output: "12"
14. Extract Specific Digits
=MID(A1, 6, 4)
Input: "1234-5678-9012" → Output: "5678"
15. Extract Between Delimiters
=MID(A1, FIND("-", A1)+1, FIND("-", A1, FIND("-", A1)+1)-FIND("-", A1)-1)Input: "ABC-123-XYZ" → Output: "123"
🎯 Pro Tips
- • Combine with FIND/SEARCH: Dynamic extraction based on delimiters
- • Use LEN: Calculate positions: RIGHT(A1, LEN(A1)-5)
- • Nested functions: Extract complex patterns with multiple steps
- • TRIM after extraction: Remove extra spaces: TRIM(LEFT(A1,5))
- • Flash Fill alternative: For simple patterns, use Excel's Flash Fill (Ctrl+E)
Frequently Asked Questions
What happens if I request more characters than available?
Excel returns all available characters without error. Example: =LEFT('Hi', 10) returns 'Hi' (only 2 chars available). No #VALUE! error. Similarly, =RIGHT('Test', 20) returns 'Test'.
How do I extract text between two specific characters?
Use MID with FIND: =MID(A1, FIND('(',A1)+1, FIND(')',A1)-FIND('(',A1)-1). This extracts text between parentheses. Example: 'Name (Code)' returns 'Code'. Adjust delimiters for your needs.
Can LEFT, RIGHT, MID work with numbers?
Yes, but they convert numbers to text. =LEFT(12345, 2) returns '12' as text, not number 12. To use in calculations, wrap in VALUE: =VALUE(LEFT(12345,2)) returns numeric 12.
Why does my extraction include extra spaces?
Source text likely has leading/trailing spaces. Wrap extraction in TRIM: =TRIM(LEFT(A1,10)) removes spaces. Or clean source first: =LEFT(TRIM(A1),10). TRIM is crucial for data imported from other systems.
How do I extract everything after a specific character?
Use RIGHT with LEN and FIND: =RIGHT(A1, LEN(A1)-FIND('-',A1)). This gets everything after '-'. Example: 'ABC-12345' returns '12345'. For multiple occurrences, use FIND with start_num parameter.
What's the difference between FIND and SEARCH in extraction?
FIND is case-sensitive, SEARCH is not. FIND('A','apple') returns error, SEARCH('A','apple') returns 1. Both find position for extraction. SEARCH also supports wildcards (* and ?). Use SEARCH for flexible matching.