What LEFT, RIGHT & MID Do
These three functions extract portions of a text string. LEFT pulls characters from the beginning, RIGHT pulls from the end, and MID pulls from any position in the middle. They are essential for parsing structured data like product codes, dates, phone numbers, and IDs that follow a consistent format.
Syntax
=LEFT(string, num_chars)
=RIGHT(string, num_chars)
=MID(string, start, length)
| Function | Parameter | Description |
|---|---|---|
| LEFT | string | The source text. |
| num_chars | Number of characters to extract from the left. Defaults to 1. | |
| RIGHT | string | The source text. |
| num_chars | Number of characters to extract from the right. Defaults to 1. | |
| MID | string | The source text. |
| start | The position of the first character to extract (1-based). | |
| length | The number of characters to extract. |
Basic Examples
Example 1: Extract Area Code
| A | B (Formula) | B (Result) | |
|---|---|---|---|
| 1 | Phone | Area Code | Area Code |
| 2 | 415-555-0198 | =LEFT(A2, 3) | 415 |
| 3 | 212-555-0134 | =LEFT(A3, 3) | 212 |
Example 2: Get File Extension
=RIGHT("report_final.xlsx", 4)
Result: xlsx
Example 3: Extract Middle Characters
Pull the department code from a structured employee ID like "US-MKT-0042":
=MID("US-MKT-0042", 4, 3)
Result: MKT — starts at position 4 and takes 3 characters.
Advanced Examples
Parse First Name from Full Name
When names follow "First Last" format, combine LEFT with FIND to extract the first name dynamically:
=LEFT(A2, FIND(" ", A2) - 1)
FIND locates the space, and LEFT takes everything before it. For "Maria Chen", FIND returns 6, so LEFT takes 5 characters: Maria.
To get the last name, use MID:
=MID(A2, FIND(" ", A2) + 1, LEN(A2))
Extract Year from a Date String
If column A has dates formatted as text like "03/15/2026":
=RIGHT(A2, 4)
Result: 2026
For the month:
=LEFT(A2, 2)
Result: 03
For the day:
=MID(A2, 4, 2)
Result: 15
Common Mistakes
- Treating MID's start as zero-based. MID uses 1-based positioning. The first character is at position 1, not 0.
=MID("Hello", 0, 3)returns an error. - Applying these to actual date or number cells. If a cell contains a real date (not text), LEFT/RIGHT/MID operate on the underlying serial number, not the displayed format. Convert first with TEXT:
=LEFT(TEXT(A2, "MM/DD/YYYY"), 2). - Hardcoding lengths for variable-length data.
=LEFT(A2, 5)works only if the target portion is always 5 characters. For variable-length parsing, combine with FIND or SEARCH to locate delimiters dynamically.
SheetAI Tip
Don't want to memorize this syntax? With SheetAI, just type what you need in plain English — like "extract the first 3 characters from each cell in column A" — and the formula is generated for you. Install SheetAI to try it free.