Left in Excel VBA: Syntax, Examples, and Common Issues

VBA & macros · Updated November 18, 2026

In Excel VBA, Left returns a specified number of characters from the beginning of a string. For example, Left("Excel VBA", 5) returns Excel; use it when a macro needs to extract a prefix, such as a product code or the first few characters of an identifier.

What Left does in Excel VBA

Left is a built-in VBA string function. It reads from the first character and moves toward the right, returning the number of characters you request. It does not search for a particular letter or word, and it does not alter the original string. The result can be assigned to a variable, written to a worksheet cell, or used as part of a condition.

For instance, a macro processing the text INV-4827 can use Left to obtain INV, the three-character prefix. That can help group records by a shared code, separate a region prefix from an ID, or check whether a text value starts with a particular sequence. The function counts characters, not words: requesting three characters from North Region returns Nor.

In Microsoft 365 for Windows, VBA runs in the desktop Excel application, not in Excel for the web. A workbook containing VBA should generally be saved in a macro-enabled format such as .xlsm if the macro needs to be retained.

Syntax and return behavior

The standard syntax is Left(string, length). The first argument is the text to read, and the second is the number of characters to return. Both arguments are required. In VBA code, use the function name without a leading equals sign; the equals sign belongs to worksheet formulas, not ordinary VBA expressions.

  • Left("Report", 2) returns Re.
  • Left("Report", 0) returns a zero-length string.
  • If the requested length is greater than the string length, Left returns the whole string. Left("OK", 8) returns OK.
  • A negative length is invalid and causes a runtime error.

The result of Left is a Variant containing a string. Its behavior is often straightforward with ordinary text, but consider the data type and contents of the value you pass in. For example, a worksheet cell containing an Excel error value is not ordinary text and may cause a type mismatch if a macro tries to convert it to a string. If a procedure can encounter errors or unexpected cell contents, check those cases before calling the function.

Practical VBA examples

This short procedure reads the value in A2 on the active sheet, takes its first three characters, and writes the result to B2. It assumes A2 contains ordinary text or a value that can be converted to text.

Sub GetThreeCharacterPrefix()
    Dim sourceText As String
    Dim prefix As String

    ' Read the value and keep its text representation.
    sourceText = CStr(ActiveSheet.Range("A2").Value)
    prefix = Left(sourceText, 3)

    ' Write the prefix beside the source value.
    ActiveSheet.Range("B2").Value = prefix
End Sub

For a list, a loop can apply the same operation to each row. This example reads values from A2 through A10 and places the first two characters in column B on the same rows. Change the worksheet and row limits to suit the workbook. The worksheet must be named Data.

Sub ExtractPrefixesFromList()
    Dim ws As Worksheet
    Dim rowNumber As Long
    Dim sourceText As String

    ' Refer to the worksheet that contains the list.
    Set ws = ThisWorkbook.Worksheets("Data")

    For rowNumber = 2 To 10
        sourceText = CStr(ws.Cells(rowNumber, 1).Value)
        ws.Cells(rowNumber, 2).Value = Left(sourceText, 2)
    Next rowNumber
End Sub

For example, if A2 contains US-104, the second procedure writes US to B2. It processes a fixed range, so it will not automatically expand when new rows are added. For a changing list, determine the last used row first and loop only through the relevant records. Also decide how blank cells should be handled; as written, a blank cell produces a blank result.

Using Left with worksheet data

A common reason to use VBA is to automate a task that could also be done with a worksheet formula. If A2 contains US-104, the worksheet formula =LEFT(A2,2) returns US. The worksheet function and the VBA function serve a similar purpose, but they are entered in different places: a formula goes in a cell, while VBA code goes in a procedure in the Visual Basic Editor.

Choose a worksheet formula when you want the result to update automatically as source cells change and you do not need a macro to perform other actions. Choose VBA when extraction is one part of a larger repeated process, such as preparing a report, copying values, or applying several transformations in sequence. A macro can also write static results, while a formula normally recalculates according to Excel’s calculation settings.

Keep in mind that a cell’s stored value may not be the same as its displayed appearance. If a numeric ID is stored as the number 123 but formatted to display as 00123, converting its value to text and taking the first characters will not necessarily preserve the displayed leading zeros. Store identifiers as text when those zeros are meaningful, or deliberately format and convert the value before extracting characters. Dates also need care: a date is stored as a number, so applying Left to its converted value does not automatically extract characters from a date display such as 2026-11-02.

Left, Left$, and worksheet LEFT

VBA also provides Left$, a string-returning form of the function. The dollar sign indicates a String return type, whereas Left returns a Variant containing a string. For typical text extraction, either form may be suitable. Using Left is common and makes the code easy to recognize; use Left$ when you specifically want a String result and have considered how the input is handled.

Do not confuse VBA’s Left with the worksheet function LEFT. In a cell, write a formula such as =LEFT(A2,3). In a VBA procedure, write an expression such as Left(sourceText, 3). The names are similar because they perform the same kind of extraction, but the syntax and execution context differ. You do not need to call Excel’s worksheet function through Application.WorksheetFunction just to extract the leftmost characters in VBA.

Common problems and how to avoid them

  • Requesting too many characters: This is usually safe; the result is simply the entire string if it is shorter than the requested length.
  • Using a negative length: Validate the value before calling Left. For example, a length supplied by a user or read from a cell may need checking so a negative number does not stop the macro with an error.
  • Expecting a search operation: Left always starts at the first character. If the desired text begins after a delimiter, find the delimiter’s position first and use a function intended to locate characters, such as InStr.
  • Unexpected spaces: Leading spaces count as characters. If values may contain accidental spaces, decide whether to remove them with Trim before extracting the prefix. Do not trim automatically if spaces are meaningful data.
  • Reading the wrong sheet: Code using ActiveSheet operates on whichever sheet is active when the macro runs. For repeatable work, refer to a specific worksheet by name, as in the list example.
  • Confusing displayed text with stored data: Number formats, dates, and leading zeros can make a cell look different from its underlying value. Check how the data is stored before relying on character positions.

When extracting codes, also check whether the source values are consistently structured. If some IDs have a two-character prefix and others have a three-character prefix, a fixed length alone will not identify the correct prefix. The appropriate rule might depend on a separator, a lookup table, or a separate field in the data.

Running a macro safely

To add VBA in Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste the procedure into the module. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon and select Developer. You can then open the editor from Developer > Visual Basic. To run a procedure from the editor, place the cursor inside it and press F5.

On a Mac, use Developer > Visual Basic if the Developer tab is available; the keyboard shortcut can vary with the keyboard and function-key settings. Excel for the web can open a workbook but cannot create, edit, or run VBA macros. If a macro must be available in the workbook, use desktop Excel and save in a macro-enabled format.

Before running a macro, save a copy of the workbook. VBA changes cannot be undone with Ctrl+Z (or Command+Z on Mac), so a mistake in a procedure that writes to cells may be difficult to reverse. Run macros only when you trust their source. On Windows, macro security options are available through File > Options > Trust Center > Trust Center Settings; do not enable macros from unknown files just to make them run.

Frequently Asked Questions

What does Left do in Excel VBA?
Left returns a chosen number of characters starting at the beginning of a string. For example, it can extract the first three characters of a product code.

How do I use Left in a VBA cell macro?
Read a cell’s value into a variable, call Left(variable, numberOfCharacters), and assign the result to the destination cell. Make sure the source contains a value that can be treated as text.

What happens if the length is longer than the text?
Left returns the whole string. It does not add spaces or raise an error just because the requested number exceeds the string’s length.

Can Left extract text after a hyphen?
Not by itself. Left always starts at the beginning. To extract text based on a hyphen, locate the delimiter and use an appropriate position-based approach, or use a worksheet formula designed for that task.

Why does Left return different characters than I see in the cell?
The cell may contain a number or date whose display is controlled by formatting, or an identifier may have lost leading zeros because it was stored as a number. Check the underlying value and decide how it should be converted before extracting characters.

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.