Left, Mid, Right in Excel VBA: How String Slicing Works

VBA & macros · Updated November 21, 2026

In Excel VBA, Left, Mid, and Right return selected parts of a text string: use Left(text, count) for characters at the start, Mid(text, start, count) for characters from a specified position, and Right(text, count) for characters at the end. They are VBA functions, not worksheet formulas, and their positions are counted from 1.

What Left, Mid, and Right do

These three functions are basic tools for extracting text in a macro. They are useful when a value has a predictable structure, such as a fixed-length product code, a prefix in an identifier, or a file extension at the end of a file name. Each function takes a string and returns another string; it does not alter the original value.

  • Left reads from the beginning of the string.
  • Mid reads from a position you specify within the string.
  • Right reads from the end of the string.

For example, taking the first four characters of INV-4827 with Left gives INV-. Taking three characters starting at position 5 with Mid gives 482. Taking four characters with Right gives 4827. The position and character count are separate: the start position tells Mid where to begin, while the count tells it how many characters to return.

Syntax and optional arguments

The standard syntax is Left(string, length), Right(string, length), and Mid(string, start, [length]). The square brackets in the documented syntax mean that the Mid length argument is optional; do not type the brackets themselves. If you omit length from Mid, it returns the characters from the start position through the end of the string.

In VBA, you will also see the dollar-sign forms Left$, Mid$, and Right$. They perform the corresponding text extraction and return a String directly. The versions without the dollar sign return a Variant containing a string. For ordinary text handling, the difference rarely changes the result, but the dollar forms make the intended string return type explicit. Choose one style and use it consistently; both are valid VBA.

The length arguments are numbers of characters, not end positions. So Mid$(text, 3, 4) means start at character 3 and return four characters: positions 3, 4, 5, and 6. It does not mean return characters 3 through 4.

How character positions work

VBA string positions begin at 1. In the text EX-731, E is position 1, X is 2, the hyphen is 3, and 7 is 4. This is worth checking whenever you translate a position from another system or from a worksheet formula: VBA’s Mid uses a one-based start position, not a zero-based index.

Spaces and punctuation count as characters too. In AB 123, the two letters, the space, and the three digits make six characters. If a string contains a tab or another character, that character also takes a position. Visual appearance can be misleading when text includes leading spaces, so inspect the actual value before choosing a start position.

For Left and Right, the length is simply how many characters to take from the selected end. A length of zero returns an empty string. A requested length greater than the string length returns the whole string. A negative length is not meaningful and causes a run-time error. For Mid, a start position less than 1 is invalid; a start position beyond the end yields no remaining characters. Use a nonnegative length when supplying one.

Examples with a single string

Suppose a macro has the string ORD-2026-0318. If the prefix is always four characters, Left$ can retrieve it. If the year is always in positions 5 through 8, Mid$ can retrieve that part. If the final four characters are a sequence number, Right$ can retrieve those. The following complete procedure displays each result in the Immediate window in the VBA editor:

Option Explicit

Sub ShowTextParts()
    Dim orderCode As String
    Dim prefix As String
    Dim yearPart As String
    Dim sequencePart As String

    ' Extract fixed-position parts from one example code.
    orderCode = "ORD-2026-0318"
    prefix = Left$(orderCode, 4)
    yearPart = Mid$(orderCode, 5, 4)
    sequencePart = Right$(orderCode, 4)

    Debug.Print prefix       ' ORD-
    Debug.Print yearPart     ' 2026
    Debug.Print sequencePart ' 0318
End Sub

The sequence is kept as text, so its leading zero remains. Converting a code part to a number would remove that zero and may be undesirable for identifiers. Use numeric conversion only when the extracted characters represent a quantity on which you intend to do arithmetic.

Using Mid when the length is not fixed

Fixed positions are convenient only when the source format is consistent. If the text before a separator changes length, a hard-coded position can extract the wrong characters. For a simple case with a known marker, find its position first with InStr, then calculate the start and length. The extraction functions do not identify words, delimiters, or field boundaries by themselves; those are separate decisions your code must make.

Another frequent need is to take everything after a known prefix. If the prefix length is stable, Mid$(text, Len(prefix) + 1) returns the remainder. Here Len counts characters and the extra 1 moves the start past the prefix. If the prefix can vary or might not be present, check that it matches before extracting. Otherwise, the code may return an unintended substring without raising an error.

Similarly, to take the text between two separators, locate both separators and use their positions to work out the starting character and character count. Account for the separator characters themselves: the first character after a separator is at its position plus one. This position arithmetic is a common source of off-by-one mistakes.

Extracting text is different from replacing it

The name Mid can refer to two related but different VBA features. Mid$(...) is a function that returns a portion of a string. The Mid statement, by contrast, can replace characters within a variable string. That statement is not the same as calling the function, and it is not usually the clearest approach for editing text of arbitrary length.

For example, assigning a new value to a variable using string concatenation is often easier to understand: preserve the part before the target, add the replacement, then append the part after it. The Mid statement is mainly relevant to fixed-length string manipulation and specialized legacy code. If you encounter it, check whether the destination string’s length and the replacement length have the intended behavior before changing the code.

VBA functions versus worksheet formulas

Excel worksheets also have functions named LEFT, MID, and RIGHT. They are used in cells, for example as formulas such as =LEFT(A2,4). In VBA, the analogous operation is written in code with a string variable or an expression, such as Left$(cellValue, 4). The names and basic purpose match, but the syntax belongs to different environments.

Use worksheet functions when the result should appear in a cell and update as worksheet inputs change. Use VBA functions when a macro needs to inspect or transform text as part of a process, such as building a report or deciding which records to handle. A VBA macro can also write its result to a cell, but the extraction itself still takes place in VBA.

Do not confuse these functions with newer text-splitting functions or with search functions. Left, Mid, and Right take positions and lengths; they do not search for a separator. A typical robust routine combines a search such as InStr with Mid, or uses a different parsing method when the format is variable.

Common problems and how to avoid them

  • Starting one character too early or late: Remember that the first character is position 1. Write out a short sample and number its characters when debugging.
  • Assuming every value has the same format: A fixed-position extraction can silently produce the wrong text if a prefix, separator, or leading space changes. Validate the input format first.
  • Passing a negative length: Check the result of any subtraction used to calculate a length. If the positions are inconsistent, the calculated length may be negative and cause an error.
  • Dropping leading zeroes: Keep identifiers as strings unless they truly represent numbers. Numeric conversion can change their displayed and stored form.
  • Ignoring blank or short values: A short string is not necessarily an error for Left or Right, but your resulting text may not meet the assumptions of later code. Validate required lengths explicitly.
  • Expecting extraction to modify the source: The functions return text; assign that result to a variable or cell if you need to store it.

When the input can contain an empty cell, remember that a cell’s value may not arrive in VBA as a straightforward String in every situation. A robust macro checks for errors or empty values before converting or extracting. Also decide how the code should treat spaces: trimming leading or trailing spaces changes the positions, so do it deliberately rather than as an accidental cleanup step.

Running a small VBA example safely

In Excel for Microsoft 365 on Windows, open the VBA editor with Alt+F11, insert a standard module through Insert > Module, and place a procedure there. Run a selected procedure with F5 from the editor. On a Mac, open the Visual Basic Editor from the Developer tab or use Option+F11 (some keyboards require Fn as well); menu availability and shortcuts can vary with keyboard settings. Excel for the web does not run VBA macros, so use the desktop application.

The example above writes to the Immediate window rather than changing worksheet cells. In the editor, open it with View > Immediate Window. For any macro that changes workbook data, save a separate copy before running it. VBA actions cannot be reversed with Ctrl+Z, and the same applies to undo on Mac. If you need to keep the macro in the workbook, save it in a macro-enabled .xlsm file and follow your organization’s macro security rules.

Frequently Asked Questions

Do Left, Mid, and Right start counting at zero or one?
They use one-based positions in VBA. The first character is position 1, including when it is a space or punctuation mark.

What happens if I omit the length in Mid?
Mid(string, start) returns the characters from the specified start position through the end of the string. The length argument is optional for Mid, but not for Left or Right.

What is the difference between Left and Left$?
Both extract characters from the start. The dollar-sign form returns a String, while the version without the dollar sign returns a Variant containing a string. The same distinction applies to the Mid and Right pairs.

Can I use these functions directly in an Excel cell?
Use the worksheet functions in a cell formula, such as =RIGHT(A2,4). VBA functions belong in VBA code, although a macro can place their results into cells.

Do Left, Mid, or Right find a word or delimiter?
No. They extract by position and length. Use a search function such as InStr to find a delimiter, then calculate the position and length for extraction.

Choosing the right function

Choose Left when the relevant characters are at the beginning, Right when they are at the end, and Mid when they begin at a known position within the string. For variable-length formats, combine extraction with a search and validation rather than relying on fixed character counts. Keeping those roles distinct makes VBA text code easier to read, safer to maintain, and less likely to break when an input changes.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.