Excel LEFT, RIGHT & MID — Extract Text by Position (and Its One Big Weakness)

TL;DR — LEFT, RIGHT and MID are three rulers that cut a string by position: LEFT(text, n) takes the first n characters, RIGHT(text, n) takes the last n, and MID(text, start, n) takes n characters starting at position start. Two things bite everyone: the result is always text — a LEFT(A2,4) that pulls out "2026" won't SUM or compare as a number until you wrap it in VALUE() — and hardcoded positions are brittle: the moment a code changes length, your slice lands in the wrong place. MID counts from 1, not 0. Works in every Excel version.

=LEFT("INV-2026-0042", 3)      ' -> "INV"
=RIGHT("INV-2026-0042", 4)     ' -> "0042"
=MID("INV-2026-0042", 5, 4)    ' -> "2026"

Every spreadsheet eventually needs to pull a piece out of a larger string — the year out of an invoice number, the area code out of a phone number, the first name out of a full name. LEFT, RIGHT and MID are the oldest, most portable way to do it, and they're worth understanding deeply because of their limits, not in spite of them.

What you'll learn

The one question to ask before every extraction: am I cutting by position or by content?
Why MID is 1-based, and how LEFT/RIGHT/MID divide up the same string
Why extracted text is text — and the VALUE() fix that keeps your math alive
Why a hardcoded position is the most fragile line in your sheet
When to stop and reach for FIND, TEXTSPLIT, or Power Query instead

The mental model: three rulers laid on the same string

Picture the string INV-2026-0042 with a ruler under it, positions numbered from 1. LEFT reads from the left edge inward, RIGHT reads from the right edge inward, and MID starts wherever you point and reads to the right. They don't search, they don't understand the data — they just count characters and cut.

'  position:  1 2 3 4 5 6 7 8 9 ...
'  string:    I N V - 2 0 2 6 - 0 0 4 2
=LEFT(A2, 3)        ' "INV"  — first 3
=MID(A2, 5, 4)      ' "2026" — 4 chars from position 5
=RIGHT(A2, 4)       ' "0042" — last 4

That's the whole idea. The power and the danger both come from the same fact: you are committing to exact positions. When the positions are guaranteed — a fixed-width code, a country prefix that's always two letters — these functions are perfect. When they're not, you're building on sand (more on that below).

The rule that unlocks everything: the result is always text

This is the trap that sends people to forums asking "why won't my extracted number add up?" LEFT, RIGHT and MID always return text, even when every character is a digit. LEFT("2026-04", 4) gives you the text "2026", not the number 2026 — so SUM, >, MIN, and a chart axis all treat it as a label.

Force it back to a number explicitly:

=VALUE(LEFT(A2, 4))     ' text "2026" -> number 2026
=--LEFT(A2, 4)          ' the same, using a double negative

The reverse is also worth knowing: because the output is text, leading zeros survive. RIGHT("0042", 4) keeps "0042" instead of collapsing to 42 — which is exactly what you want for ZIP codes, account numbers, and SKUs, and exactly what you must undo with VALUE() when you actually need arithmetic.

MID counts from 1, and num_chars is forgiving

MID(text, start_num, num_chars) is 1-based: MID("Excel", 1, 2) is "Ex", not "xc". Off-by-one here is the single most common MID mistake — if your slice is shifted by one character, check whether you assumed 0-based counting from another language.

Two small mercies keep LEFT/RIGHT/MID from erroring out:

Omit num_chars on LEFT/RIGHT and it defaults to 1.
Ask for more characters than exist and you get the rest of the string back, no error. RIGHT("AB", 10) returns "AB", not #VALUE!.

So these functions rarely crash — they fail quietly by returning the wrong slice, which is worse. That's the real risk, and it leads straight to their one big weakness.

The one big weakness: hardcoded positions are fragile

=RIGHT(A2, 4) to grab the last four digits works beautifully — right up until a record arrives with a 5-digit suffix, and now you're silently dropping a digit from every value in the column. A position you typed by hand only stays correct while the data keeps the exact shape you assumed. Real-world data doesn't.

The robust pattern isn't a longer MID — it's to stop cutting by position and start cutting by content: find the delimiter, then slice relative to it.

' Fragile: assumes the name is always "First Last" with the space at 6
=LEFT(A2, 5)

' Robust: cut at wherever the first space actually is
=LEFT(A2, FIND(" ", A2) - 1)        ' first word, any length
=MID(A2, FIND("@", A2) + 1, 99)     ' everything after the @ in an email

That FIND(" ", A2) is the hinge. LEFT/MID/RIGHT answer "give me characters here"; FIND and SEARCH answer "where is the landmark I should cut at?" Combined, they turn a brittle fixed-position slice into one that follows the data. Learn FIND next — it's what makes these three functions reliable instead of merely convenient.

The judgment call: when to use position, and when not to

Use bare LEFT/RIGHT/MID when the position is genuinely fixed — a 3-letter currency code, a fixed-width legacy export, the first two characters of a state abbreviation. Pair them with FIND/SEARCH the moment the cut point depends on the content of the string.

And know when to put them down entirely. If you're nesting LEFT/MID/RIGHT and FIND three deep just to break a string at its delimiters, that's a sign you want TEXTSPLIT (Excel 365), which splits on a delimiter in one readable formula. For a one-time cleanup of a large import, Text to Columns or Power Query is leaner than a column of fragile formulas. LEFT/MID/RIGHT are scalpels — precise, but only as good as your aim.

How ExcelMaster helps

The hard part of extraction is rarely the function — it's getting the positions right and keeping them robust. ExcelMaster takes a plain-English request like "pull the year out of the invoice number and the domain out of the email" and writes the formula with the correct FIND anchors, a VALUE() wrap when the result needs to be a number, and a note when a fixed position would break on the next odd row. You describe what to extract; it handles the character counting.

Frequently asked questions

What's the difference between LEFT, RIGHT and MID?

LEFT(text, n) returns the first n characters, RIGHT(text, n) returns the last n, and MID(text, start, n) returns n characters beginning at position start. LEFT and RIGHT cut from an edge; MID cuts from anywhere you point.

Why does my extracted number not add up?

Because LEFT, RIGHT and MID always return text, even when it looks like a number. Wrap the result in VALUE() — =VALUE(LEFT(A2,4)) — or prefix it with a double negative =--LEFT(A2,4) to convert it to a real number.

Is MID 0-based or 1-based?

1-based. MID("Excel", 1, 2) returns "Ex". The first character is position 1, not 0 — a common source of off-by-one errors for programmers used to other languages.

How do I extract text up to a specific character?

Combine LEFT with FIND: =LEFT(A2, FIND(" ", A2) - 1) returns everything before the first space. This is far more robust than a hardcoded length because it follows the actual position of the character.

What happens if I ask MID for more characters than the string has?

Nothing breaks — MID (and RIGHT/LEFT) simply return as much as exists. RIGHT("AB", 10) returns "AB", not an error. This is convenient, but it also means a wrong length fails silently instead of warning you.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-06-29.