VBA Mid, Left & Right in Excel — Extract a Substring by Position

Tested on: Excel 365 v2509 · Excel 2021 · Excel 2019 · last verified 2026-06-09

TL;DR — Mid, Left and Right pull a piece out of a string by position — a start point and a length. Left and Right are just Mid pinned to one end:

Sub SliceDemo()
    Dim s As String
    s = "INV-2026-0042"

    Debug.Print Left(s, 3)      ' INV       <- first 3 characters
    Debug.Print Right(s, 4)     ' 0042      <- last 4 characters
    Debug.Print Mid(s, 5, 4)    ' 2026      <- 4 chars starting at position 5
    Debug.Print Mid(s, 5)       ' 2026-0042 <- no length = to the end
End Sub

One thing to memorise before anything else: VBA strings start at position 1, not 0.

The mental model: cut by ruler, not by content

Mid is a ruler. You tell it where to start and how many characters to take, and it cuts. It does not look at what the characters are — it counts positions. Left(s, n) is "the ruler from the start," Right(s, n) is "the ruler from the end," and Mid(s, start, len) is the general case both of them are shortcuts for.

That single idea tells you exactly when these are the right tool and when they are not. They shine when the thing you want sits at a fixed, known position — a 3-letter prefix, the last 4 digits, a fixed-width report column. They fall apart the moment the piece can move, because a ruler does not adapt. That is the whole tension of this article, and the reason InStr and Split exist.

The one rule: it is 1-indexed, and that off-by-one is the #1 bug

Coming from almost any other language, your fingers type 0-based positions. VBA does not work that way:

In Mid(string, start, length), start = 1 means the first character. Mid("Excel", 1, 2) is "Ex", not "xc".

Get this wrong and you do not get an error — you get the wrong characters, shifted by one, which is far worse than a crash because it looks plausible:

Debug.Print Mid("Excel", 1, 2)   ' "Ex"  (correct — 1 is the first char)
Debug.Print Mid("Excel", 0, 2)   ' run-time error 5 — there is no position 0
Debug.Print Mid("Excel", 2, 2)   ' "xc"  (start at the 2nd char)

The companion rule: Mid with no length returns everything from start to the end. That is not a bug to work around — it is the cleanest way to say "the rest of the string from here":

extension = Mid(filename, InStrRev(filename, ".") + 1)   ' everything after the last dot

The thing almost nobody knows: Mid is also a statement

There are two Mids. The function reads characters out. The statement writes characters in place — it overwrites part of a string without changing its length:

Dim s As String
s = "2026-00-15"
Mid(s, 6, 2) = "06"      ' overwrite 2 chars starting at position 6
Debug.Print s            ' "2026-06-15"  <- same length, patched in place

Mid(s, 6, 2) = "06" on the left of an = is the statement. It is faster than rebuilding the string with Left & new & Right, and it is the idiomatic way to patch a fixed-width field. The catch that matches the mental model: it never changes length — assign a longer replacement and VBA simply truncates it to fit the window. The ruler does not stretch.

The Left/Right/Mid family, in one block

Left(s, n)          ' first n characters
Right(s, n)         ' last n characters
Mid(s, start)       ' from start to the end
Mid(s, start, len)  ' len characters from start
Len(s)              ' how many characters total (use this to bound your cuts)

Left and Right are not separate ideas — they are Mid with one coordinate fixed. Left(s, n) is Mid(s, 1, n). Right(s, n) is Mid(s, Len(s) - n + 1). Once Mid clicks, the other two are free.

The opinion: hardcoded positions are the most brittle line in your macro

Mid(s, 5, 4) reads beautifully on the one row you tested. Then a value comes in one character shorter, every position shifts, and your "year" extraction now returns "026-". Position-based cuts encode an assumption — the data is always shaped exactly like my sample — that real data breaks the first chance it gets.

My rule: use Left/Right/Mid only when the position is genuinely fixed — a format you control, a fixed-width export, a code with a guaranteed layout. The moment the boundary depends on content (the text before the first space, the part after the dash, the bit between two markers), stop hardcoding numbers. Find the boundary with InStr and feed that into Mid, or use Split if there is a clean delimiter:

' Brittle: assumes the dash is always at position 4
code = Left(s, 3)

' Robust: find the dash, cut there — works regardless of length
code = Left(s, InStr(s, "-") - 1)

The second version is one function longer and survives contact with reality. That trade is almost always worth it.

When to use which

Common Mid/Left/Right mistakes (and the fix)

When the slicing logic takes over — describe the result instead

Pulling the invoice number out of one cell is two lines. Pulling it out of 40,000 rows that come in three slightly different formats — some with the dash, some without, some with a stray prefix — is a tangle of InStr, Mid, length checks and fallbacks that buries the actual intent. ExcelMaster Agent lets you say "extract the 4-digit year from the invoice code, whatever the surrounding format" and generates Python that handles the variants and backs up your workbook first — no off-by-one, no hardcoded positions. Try it free →

Related guides

• VBA Split — Turn One String Into an Array
• VBA Replace — Swap Text & the Traps in Its Arguments
• VBA InStr — Find Text Inside a String
• VBA For Loop in Excel — 8 Real-World Examples

FAQ

What is the difference between Mid, Left and Right in VBA? All three extract a substring by position. Left(s, n) takes the first n characters, Right(s, n) the last n, and Mid(s, start, len) takes len characters starting at start. Left and Right are special cases of Mid pinned to one end of the string.

Is VBA Mid 0-based or 1-based? 1-based. Mid("Excel", 1, 2) returns "Ex" — position 1 is the first character. Using 0 as the start raises run-time error 5. This off-by-one is the most common Mid bug for people coming from 0-based languages.

How do I get everything after a position with Mid? Omit the length argument: Mid(s, start) returns the substring from start to the end of the string. Combined with InStr, Mid(s, InStr(s, "-") + 1) gives you everything after the first dash.

What is the Mid statement in VBA? Mid(s, start, length) = "text" on the left of an assignment overwrites characters in place, without changing the string's length. It's faster than rebuilding the string and ideal for patching fixed-width fields — but a replacement longer than the window is truncated.

Why is my Mid extraction wrong on some rows? Almost always a hardcoded position that assumes every value has the same length. When the boundary depends on content rather than a fixed offset, find it with InStr and pass that into Mid, or use Split if there's a delimiter.