VBA Left Function: How to Return Text from the Start

VBA & macros · Updated November 18, 2026

In VBA, use Left(string, length) to return a specified number of characters from the beginning of a string. For example, Left("AB-8421", 2) returns AB; if the requested length is longer than the text, VBA returns the whole string.

What Left Does in VBA

Left is a built-in VBA function for taking text from the left-hand end of a string. It does not search for a particular character or word: it simply counts from the first character and returns the requested number. This makes it useful for extracting a code prefix, checking the start of a filename, or separating a fixed-width field.

The function returns a new value; it does not shorten or otherwise change the original variable. You can assign the result to another variable, compare it with a known prefix, or write it to a worksheet cell. VBA string positions start at the beginning of the text, so a length of 1 returns the first character, a length of 2 returns the first two, and so on.

For example, if a cell contains NW-5048, taking the first two characters gives NW. Taking the first three gives NW-, including the hyphen. That detail matters when a delimiter is part of the prefix you need.

Syntax and Return Behavior

The syntax is Left(string, length). The first argument is the text or string expression to read. The second is the number of characters to return, and it must be a numeric expression. In ordinary code, store the requested count in a Long variable or pass a whole-number literal.

These rules cover the most common cases:

  • A length of 1 returns the first character.
  • A length of 0 returns a zero-length string, written as "".
  • A length equal to the string’s length returns the entire string.
  • A length greater than the string’s length also returns the entire string; it does not add padding.
  • A negative length causes a run-time error, so validate counts that come from user input or worksheet data.

The regular Left function returns a Variant containing a String value for normal text input. The related Left$ form returns a String directly. Both are commonly used for string extraction; the dollar-sign form is a type-declaration variation, not a different way of counting characters.

Basic VBA Examples

This example assigns the first two characters of a product code to a separate variable. The original value remains unchanged.

Sub GetProductPrefix()
    Dim productCode As String
    Dim prefix As String

    productCode = "AB-8421"
    prefix = Left$(productCode, 2)

    Debug.Print prefix  ' Prints AB
End Sub

To include the hyphen, change the requested length to 3. To take the first five characters, use 5. If the input is shorter than the requested count, the result is simply the available text, which can be convenient when data is not a uniform length.

You can also use Left in a condition. This example checks whether a value begins with a particular two-character code. The comparison shown is case-sensitive under VBA’s default binary comparison setting; the project’s Option Compare setting can affect string comparisons.

Sub CheckPrefix()
    Dim itemCode As String

    itemCode = "NW-5048"

    If Left$(itemCode, 2) = "NW" Then
        Debug.Print "Northwest item"
    End If
End Sub

Using Left in an Excel Macro

A common Excel task is to read text from one column and place its prefix in another. The following macro reads values in column A of a worksheet named Data, starting at row 2, and writes the first three characters to column B. It assumes the source cells contain text or values that can safely be converted to text, and that the worksheet exists.

Sub WritePrefixes()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rowNumber As Long
    Dim cellText As String

    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For rowNumber = 2 To lastRow
        cellText = CStr(ws.Cells(rowNumber, "A").Value2)
        ws.Cells(rowNumber, "B").Value = Left$(cellText, 3)
    Next rowNumber
End Sub

If the source column has a header in row 1, the loop skips it. If there is no data beneath the header, the loop does not process any rows. Before running a macro that writes to a workbook, save a copy: VBA macros cannot be undone with Ctrl+Z. The same caution applies when you run code from the Visual Basic Editor or assign it to a button.

Handling Lengths, Blank Text, and Null

When the length is calculated rather than typed into the code, check that it is valid before calling Left. For example, a value read from a cell may be blank, negative, or otherwise unsuitable. A negative count can stop the macro with a run-time error, so a simple guard can prevent that failure.

For a String variable containing "", Left$(text, 3) returns "". A blank worksheet cell read through Value2 can be converted to an empty string with CStr, but a cell containing an Excel error value is different: converting that error to a String can fail. If a column may contain errors, test for them before conversion, for example with IsError.

VBA’s Null value also needs deliberate handling. The regular Left function propagates Null when its string argument is Null; code that expects a usable string should test with IsNull first. Do not confuse Null with an empty string: an empty string has zero characters, while Null represents the absence of a valid value in a Variant.

For ordinary text, you can use Len to find the string’s length and compare it with the requested count. This is useful when your code needs to distinguish a complete prefix from a short input, rather than simply accepting the shorter result that Left returns.

Left, Left$, and LeftB

Left and Left$ take the same arguments and ordinarily produce the same visible text. Choose Left when you specifically need its Variant return type; choose Left$ when a String result is appropriate. In typical Excel VBA procedures that process text, either form is suitable, but using the dollar-sign version makes the intended result type explicit.

LeftB is a different, byte-oriented function. It is intended for cases involving byte data stored in strings, including certain legacy work with double-byte character sets. It does not mean “take the first characters” in the ordinary text-processing sense. Use Left or Left$ for names, labels, product codes, and other text; byte-based operations require careful attention to the character encoding and the data being handled.

Common Problems and Troubleshooting

  • The result includes a separator: The count includes every character, including spaces, hyphens, and punctuation. Reduce the count if the separator should not be part of the result.
  • The result is shorter than expected: Check the actual source value and whether it contains leading spaces. Left starts at the first character, even if that character is a space.
  • The macro errors on a negative count: Validate the count before calling the function. An invalid length is a common cause of a run-time error.
  • A cell with an error value breaks conversion: Test the cell value with IsError before using CStr or passing it to a string operation.
  • The prefix check misses lowercase text: Decide whether case should matter and use a consistent comparison approach, such as converting both values to the same case before comparing.

Leading or trailing spaces are not removed automatically. If the data may contain unwanted spaces, decide whether to trim it first. Trimming changes what counts as the first character, so only do it when whitespace is not meaningful in the source data.

VBA Left Compared with Excel’s LEFT Function

Excel worksheets have a function named LEFT that performs a similar extraction inside a cell formula. In VBA code, use Left or Left$; in a worksheet formula, use LEFT. The worksheet function is useful when results should update automatically as source cells change. VBA is useful when a macro needs to process values, make decisions, or write results in a batch.

With the source text INV-204 in cell A2, enter this formula in B2 to return INV: =LEFT(A2,3). Fill it down to apply it to additional rows. This is a worksheet formula, not VBA syntax. In Excel for Microsoft 365 on Windows, the formula is entered directly in the cell or formula bar; the same formula is available in Excel for Mac and Excel for the web.

Practical Uses and Alternatives

Use Left when the number of characters is known or has already been calculated. Common uses include extracting a country or department code from a consistently formatted identifier, testing a file-name prefix, or taking a fixed-width field from imported text. Before relying on a fixed count, confirm that the source format is consistent; a changed code format can make a technically valid result misleading.

If you need to find text up to a delimiter whose position can vary, a fixed character count may be the wrong tool. You can use VBA’s InStr function to locate the delimiter and then pass the position minus one as the length to Left$. For worksheet work, functions such as TEXTBEFORE can be more direct in Microsoft 365 or Excel 2024. If you need a section from the middle or end of text, consider VBA’s Mid or Right instead.

Frequently Asked Questions

How do I get the first three characters of a string in VBA?
Use Left$(myText, 3), replacing myText with your String variable. If the text has fewer than three characters, the function returns all the characters it has.

What happens if the length is longer than the text?
VBA returns the entire string. It does not raise an error or fill the result with spaces. A negative length, by contrast, is invalid and can cause a run-time error.

Is VBA Left the same as Excel LEFT?
They perform the same basic task of returning characters from the beginning of text, but they are used in different places. Use Left or Left$ in VBA statements and LEFT in a worksheet formula.

Should I use Left or Left$?
For normal text extraction, either is usually fine. Left$ explicitly returns a String, while Left returns a Variant containing a string for ordinary text input. Choose based on the type your procedure expects.

Does Left remove spaces or punctuation?
No. It counts spaces and punctuation just like letters. If you want to ignore leading spaces, trim the text first; if you want to exclude a delimiter, calculate the length based on that delimiter’s position.

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.