How to Use LEFT, RIGHT, and MID in Excel
LEFT, RIGHT, and MID extract characters from text. Use them to clean IDs, split codes, pull dates, and standardize imported data.
Reviewed by Griddy
Updated for current Excel and Google Sheets workflows, with examples chosen to map back to real spreadsheet tasks rather than abstract formula syntax.
LEFT, RIGHT, and MID extract specific characters from text. They help when imported data arrives as combined codes, messy IDs, account strings, or labels that need cleanup before lookup or reporting.
Use these functions when the structure is predictable: the first three characters always mean region, the last four always mean year, or the middle segment always contains a project code.
The syntax
=LEFT(text, [num_chars])=RIGHT(text, [num_chars])=MID(text, start_num, num_chars)- text - the cell containing the text
- [num_chars] - how many characters to return
- start_num - the character position where MID should start
If A2 contains WEST-0426-INV, then:
=LEFT(A2, 4)returns WEST.
=RIGHT(A2, 3)returns INV.
=MID(A2, 6, 4)returns 0426.
Example: split invoice codes
Suppose your invoice export uses codes like:
EAST-1042-PAID
WEST-1188-DUE
You want separate fields for region, invoice number, and status.
Step 1. Extract the region
=LEFT(A2, 4)For EAST-1042-PAID, this returns EAST.
Step 2. Extract the invoice number
=MID(A2, 6, 4)This starts at character 6 and returns 4 characters, so it returns 1042.
Step 3. Extract the status
=RIGHT(A2, 4)For EAST-1042-PAID, this returns PAID. For WEST-1188-DUE, the same formula would be wrong because DUE has only three characters.
⚠ WATCH OUT
When these formulas break
These functions count character positions. They do not understand words, separators, or business meaning. Excel is just counting.
That means the formula can break if:
- one region has three letters and another has four
- invoice numbers change from four digits to five
- a source system adds a prefix
- spaces appear before or after the code
When the delimiter is reliable, newer Excel users may prefer TEXTSPLIT. LEFT, RIGHT, and MID are still useful when the layout is fixed and compatibility matters.
Common issues
| Issue | Cause | Fix |
|---|---|---|
| Wrong characters returned | Start position is off by one | Count the characters including hyphens and spaces |
| Formula works for one row only | Segment lengths vary | Use delimiter-based splitting instead |
| Hidden spaces appear | Source data has leading or trailing spaces | Wrap the source in TRIM |
| Number loses leading zeros | Extracted text is converted to a number | Keep the result as text |
The Griddy way
Text extraction is easy for one row. A messy export with 600 rows is a different story.
"Split these invoice codes into region, invoice number, and status columns"
Griddy can inspect the pattern, choose fixed-position or delimiter-based formulas, and fill the cleaned fields across the sheet.
Skip the manual work
Describe it. Griddy does it.
Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and Google Sheets.
Use this on real templates
Clean imported IDs before they break the workflow
Text extraction is useful when expense exports, invoice codes, and receipt records arrive with multiple fields packed into one cell.
Expense Tracker
Log every expense, track receipts, and generate category summaries. Free template for personal or business use.
Expense Tracker for Contractors
Track contractor expenses, materials, mileage, job costs, receipts, and reimbursable purchases in one free spreadsheet template.
Invoice Template
Professional invoice template with automatic subtotal, tax, and total calculations. Customise with your logo and send in minutes.
Receipt Tracker Template
Track receipts, proof of purchase, missing documentation, owners, amounts, and follow-up status in one free spreadsheet template.