Excel LEFT Function

The Excel LEFT function returns the specified number of characters from a specified string, starting from the left side

Example: Excel LEFT Function

METHOD 1. Excel LEFT Function using hardcoded values

EXCEL

=LEFT(B5)

Result in cell D5 (a) - returns the first character from the left side of the specified string as no num_chars is specified.

=LEFT(B6,3)

Result in cell D6 (abc) - returns the first three characters from the left side of the string.

=LEFT(B7,3)

Result in cell D7 (ab) - returns the first three characters from the left side of the string, including the space between characters.

=LEFT(B8,4)

Result in cell D8 (4280) - returns the first four characters from the left side of the string, which are the numeric date values.

METHOD 2. Excel LEFT Function using links

EXCEL

=LEFT(B5)

Result in cell D5 (a) - return the first character from the left side of the specified string as no num_chars is specified.

=LEFT(B6,C6)

Result in cell D6 (abc) - return the first three characters from the left side of the string.

=LEFT(B7,C7)

Result in cell D7 (ab) - return the first three characters from the left side of the string, including the space between characters.

=LEFT(B8,C8)

Result in cell D8 (4280) - return the first four characters from the left side of the string, which are the numeric date values.

METHOD 3. Excel LEFT function using the Excel built-in function library with hardcoded values

EXCEL

Formulas tab > Function Library group > Text > LEFT > populate the input boxes

=LEFT(B6,3)
Note: in this example we are populating all of the input boxes associated with the LEFT function.

METHOD 4. Excel LEFT function using the Excel built-in function library with links

EXCEL

Formulas tab > Function Library group > Text > LEFT > populate the input boxes

=LEFT(B6,C6)
Note: in this example we are populating all of the input boxes associated with the LEFT function.

Built-in Excel LEFT Function using links

METHOD 1. Excel LEFT function using VBA with hardcoded values

VBA

Sub Excel_LEFT_Function_Using_Hardcoded_Values()

'declare a variable
Dim ws As Worksheet

Set ws = Worksheets("LEFT")

'apply the Excel LEFT function
ws.Range("D5") = Left(ws.Range("B5"), 1) 'can't have a blank num_chars argument in the VBA code
ws.Range("D6") = Left(ws.Range("B6"), 3)
ws.Range("D7") = Left(ws.Range("B7"), 3)
ws.Range("D8") = Left(ws.Range("B8"), 4)'Note: this will return 15/0 given that the VBA code recognises cell ("B8") as a text string, not date. The VBA code only recognises a date in United States format and this is written in English style

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.

PREREQUISITES
Worksheet Name: Have a worksheet named LEFT.

ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("D5"), ("D6"), ("D7") and ("D8") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.

ADDITIONAL NOTES
Note 1: You can't have a blank num_chars argument field in the VBA code and therefore needs to be populated with a value. In this example we populated the num_chars argument with a value of 1 which will return the same value (a) as shown in the Excel LEFT Function Example above.
Note 2: The output in cell ("D8") will be 15/0 given that the VBA code recognises cell ("B8") as a text string, not date. The VBA code only recognises a date in United States format and this is written in English style.

METHOD 2. Excel LEFT function using VBA with links

VBA

Sub Excel_LEFT_Function_Using_Links()

'declare a variable
Dim ws As Worksheet

Set ws = Worksheets("LEFT")

'apply the Excel LEFT function
ws.Range("D5") = Left(ws.Range("B5"), ws.Range("C5"))'Note: this will return a blank result as the VBA code does not default the empty cell ("C5") to 1
ws.Range("D6") = Left(ws.Range("B6"), ws.Range("C6"))
ws.Range("D7") = Left(ws.Range("B7"), ws.Range("C7"))
ws.Range("D8") = Left(ws.Range("B8"), ws.Range("C8"))'Note: this will return 15/0 given that the VBA code recognises cell ("B8") as a text string, not date. The VBA code only recognises a date in United States format and this is written in English style

End Sub

PREREQUISITES
Worksheet Name: Have a worksheet named LEFT.

ADDITIONAL NOTES
Note 1: The output in cell ("D5") will be an empty cell given that the VBA code does not default the empty cell ("C5") to 1.
Note 2: The output in cell ("D8") will be 15/0 given that the VBA code recognises cell ("B8") as a text string, not date. The VBA code only recognises a date in United States format and this is written in English style.

Usage of the Excel LEFT function and formula syntax

EXPLANATION

DESCRIPTION
The Excel LEFT function returns the specified number of characters from a specified string, starting from the left side.

SYNTAX
=LEFT(text, [num_chars])

ARGUMENTS
text: (Required) The string from which to extract the characters.
num_chars: (Optional) The number of characters to extract from the specified string, starting from the left side.

ADDITIONAL NOTES
Note 1: The default num_chars argument is 1. Therefore, if the argument is empty the Excel LEFT function will default to value 1.
Note 2: The LEFT function ignores the number formatting.
Note 3: In reference to date format the LEFT function recognises its numeric value, only if the date is written in United States format.
Note 4: The LEFT function ignores decimal places.