In Excel VBA, Left returns a specified number of characters from the start of a string: for example, Left("Invoice-8472", 7) returns Invoice. Use it in a macro or VBA function when you need to read or keep a text value’s leading characters; use Excel’s worksheet LEFT function when you need a result directly in a cell.
What the VBA Left Function Does
Left is a built-in VBA string function. It takes text and a character count, then returns the characters beginning at the left edge of that text. The count includes spaces and punctuation, not just letters or digits. For instance, the first four characters of " AB-92" are two spaces, A, and B.
This can be useful when a text field follows a consistent format. You might extract a two-letter region code from an ID, keep the first three characters of a product code, or check whether a file name begins with a known prefix. The function does not understand the meaning of the text: it simply counts from the beginning.
VBA strings are one-based in the sense that the first character is at the start of the string; you do not specify a starting position for Left. To take characters from another position, use a different function such as Mid. To take characters from the end, use Right.
Syntax and Arguments
The syntax is Left(string, length). The first argument is the text expression to read, and the second is how many characters to return. length should be a nonnegative whole-number count. The function returns a Variant containing a string, except when the input is Null, in which case Left returns Null.
string: A string value, variable, or expression. It can be a literal such as"North-15"or a value read from a worksheet cell.length: The number of characters to take from the beginning. A count of zero produces a zero-length string.
If the requested count is longer than the text, Left returns the entire text; it does not add spaces or raise an error just because the string is short. For example, asking for ten characters from "Cat" returns "Cat". A negative count is different: it causes a run-time error, so validate a count that could come from user input before calling the function.
Basic VBA Examples
In the Visual Basic Editor (VBE), place a procedure in a standard module to run it as a macro. On Windows, open the editor with Alt+F11. On a Mac, use Option+F11; some keyboards require Fn as well. You can also open the VBE from the Developer tab by choosing Visual Basic. Before running any macro that changes a workbook, save a copy: VBA macros cannot be undone with Ctrl+Z.
This small macro puts the result in a message box. The count is seven, so the returned value is the first seven characters:
Sub ShowLeftExample()
Dim sourceText As String
Dim resultText As String
' Take the first seven characters of the sample text.
sourceText = "Invoice-8472"
resultText = Left(sourceText, 7)
MsgBox resultText
End Sub
The message box displays Invoice. The same function can assign a value to a variable, compare a prefix, or write a result to a worksheet; it does not have to display anything.
For repeated work, Left$ is the string-returning form. This example keeps the first three characters of a product code:
Sub ShowProductPrefix()
Dim productCode As String
Dim prefix As String
' Keep the three-character prefix.
productCode = "QRT-2085"
prefix = Left$(productCode, 3)
MsgBox prefix
End Sub
The displayed prefix is QRT. The dollar sign is part of the VBA function name, not a currency symbol.
Using Left with Worksheet Cells
A macro can pass a cell’s value to Left, then place the result in another cell. For example, if A2 contains NW-104, a macro can copy its first two characters to B2. The code below converts the cell value to text before taking the prefix, which makes the intended text operation explicit. Save a copy of the workbook before running it because a macro’s changes cannot be reversed with Ctrl+Z.
Sub WritePrefixToCell()
Dim sourceText As String
Dim prefix As String
' Read A2, keep two characters, and write the result to B2.
sourceText = CStr(ActiveSheet.Range("A2").Value)
prefix = Left$(sourceText, 2)
ActiveSheet.Range("B2").Value = prefix
End Sub
With NW-104 in A2, B2 receives NW. This example uses ActiveSheet, so it acts on whichever worksheet is active when you run it. In a workbook used by other people, it is often safer to refer to a named worksheet explicitly, such as ThisWorkbook.Worksheets("Data"), rather than rely on the active sheet. Also decide how the macro should handle blank cells, errors, and numeric values before applying it to a full column.
Left and Left$ Compared
Both Left and Left$ take the same arguments and return the same visible characters for ordinary string input. The practical distinction is the declared return type: Left returns a Variant, while Left$ returns a String. When the result is known to be text, VBA programmers often choose Left$ to make that type clear. If you are following existing code that uses Left, there is usually no need to change it solely for style.
The dollar sign does not mean the function is a separate worksheet feature. Both names are VBA functions, and neither is typed as a worksheet formula in a cell. In code that may receive Null values from a database or another source, think about how those values should be handled before using a string-returning function. A worksheet cell that looks blank and a VBA Null value are not necessarily the same thing.
VBA Left Versus Excel’s LEFT Function
Excel also has a worksheet function named LEFT. It performs a similar text extraction but is used in a cell formula, for example =LEFT(A2,2) when A2 contains NW-104. The cell displays NW. In VBA code, use Left or Left$ instead; the shared name can be confusing, but the context makes clear which environment is being used.
- Choose worksheet
LEFTwhen you want a formula result that updates as the referenced cell changes. - Choose VBA
Leftwhen a macro or user-defined VBA procedure needs to process text as part of a larger task. - Choose a formula for a transparent, row-by-row calculation that worksheet users can inspect; choose a macro when automation or multi-step actions are genuinely useful.
Excel for the web can display and edit many workbook contents, but it cannot create, edit, or run VBA macros. Use desktop Excel to work with VBA. Macros are also separate from worksheet formulas: if a workbook needs to retain VBA code, save it in a macro-enabled format such as .xlsm, not as a macro-free .xlsx file.
Common Problems and How to Avoid Them
- Getting too many or too few characters: Count every character, including spaces, hyphens, and leading zeros. If the format is not fixed, first locate a delimiter with
InStror clean the text rather than assuming a fixed prefix length. - Passing a negative count: A negative
lengthtriggers a run-time error. Check that an input count is zero or greater before callingLeft. - Confusing a blank with a space: A cell that appears empty may contain spaces.
Leftcounts those spaces. If trimming is appropriate for the data, useTrimdeliberately; do not remove spaces automatically if they are meaningful. - Expecting the function to find a separator:
Leftalways counts a set number of characters. It does not stop at a hyphen or return everything before a comma. Find the separator position first, then use that position to determine the count. - Using an unqualified active sheet: A macro may write to the wrong sheet if the user changes the active tab. Refer to a specific worksheet when the destination must be predictable.
- Looking for the result in a cell: Calling
Leftin a Sub does not put the result on the sheet automatically. Assign the returned value to a variable, a cell, or another destination.
For data with inconsistent separators, one reliable pattern is to find the first separator and use the number of characters before it. If no separator is found, return the whole text or handle the record another way. That decision should reflect the data rules, rather than silently assuming every value has the expected format.
When Left Is Not the Right Tool
Use Mid when the characters of interest begin somewhere other than the first character, and use Right when they are at the end. Use InStr when the position of a character or substring matters. For example, extracting text before a hyphen requires finding the hyphen’s position; a fixed call such as Left$(text, 3) only works if the required prefix is always exactly three characters long.
Another limitation is that Left extracts characters, not structured pieces of data. If an identifier contains several fields separated by delimiters, consider splitting it into fields or using a worksheet formula suited to the pattern. For one-off analysis, a worksheet formula may be easier to review. For repeated processing, a VBA procedure can centralize the logic, but it should make its assumptions and its response to unexpected input clear.
Frequently Asked Questions
What does Left( text, 3 ) return in VBA?
It returns the first three characters of the text, including spaces and punctuation. If the text has fewer than three characters, it returns the whole text. The spaces shown around the arguments in the question are optional; the usual syntax is Left(text, 3).
What is the difference between Left and Left$ in VBA?
Both extract leading characters. Left returns a Variant, while Left$ returns a String. For normal string input, their displayed results are the same.
How do I use Left in an Excel cell?
Use the worksheet formula =LEFT(A2,3) to return the first three characters from A2. That is a cell formula, not a VBA statement. In VBA code, call Left or Left$.
What happens if the length is zero or negative?
A length of zero returns an empty string. A negative length causes a run-time error, so validate counts that come from a cell, form, or user entry.
Can Excel for the web run a VBA Left macro?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to use VBA; the worksheet LEFT formula is a separate option for cell-based results.
Related
- Excel Split Cell Data Into Multiple Columns
- Excel Split Cell Into First and Last Name: A Clear Guide
- Excel Split Cell by Space Delimiter: Methods and Tips
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Dictionary Count: What .Count ReturnsIn Excel VBA, get the number of entries in a Dictionary with its .Count property: use dict.Count. It…
- Dim Worksheet VBA: Declare and Set a Sheet VariableIn Excel VBA, use Dim ws As Worksheet to declare a variable that can refer to a worksheet;…
- VBA MsgBox If: Check a User’s Choice with an If StatementIn VBA, use MsgBox inside an If statement to show a prompt and run different code according to…
- Excel VBA: Loop Through All Files in a FolderTo loop through files in a folder with Excel VBA, use the Dir function to get the first…
- Excel VBA InputBox and IME: How Text Entry Works in WindowsExcel VBA’s built-in InputBox accepts text entered with a Windows IME (Input Method Editor), such as Japanese, Chinese,…
- Excel VBA InputBox Integer: Get and Validate Whole NumbersYes—VBA can ask for an integer with an input box, but neither common input-box option automatically guarantees a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.