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.
Leftstarts 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
IsErrorbefore usingCStror 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
- Excel Data Validation for Dates: Rules and Examples
- Data Validation Checks in Excel: Rules and Examples
- Data Validation Checklist for Excel: What to Check
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA ArrayList: How to Create and Use It in Excel VBAIn VBA, an ArrayList is a resizable, zero-based list that you can create on Windows with CreateObject("System.Collections.ArrayList"). It…
- Excel VBA Pivot Table Filter Multiple Values at OnceTo filter a standard Excel PivotTable to several items with VBA, clear the field’s existing filter and set…
- Excel VBA: How to Check AutoFilter StatusIn Excel VBA, check Worksheet.FilterMode to find out whether a worksheet currently has a filter applied, and check…
- Combobox in VBA UserForms: Setup, Code, and TipsA ComboBox in a VBA UserForm lets a person choose an item from a drop-down list, and it…
- Unload UserForm VBA: Close Forms and Reset State SafelyIn VBA, use Unload UserForm1 to close and unload a form, or use Unload Me from code inside…
- Excel VBA: Find the Bottom Row of Data in a SheetTo find the bottom row in a particular Excel column, start at the worksheet’s last row and move…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.