How to☝️ Use the LEFT Function in Excel

Description

The Excel LEFT function returns a specified number of characters from the beginning of a text string.

For instance, =LEFT(“Excel”, 2) returns “Ex”.

In plain English, the LEFT function picks the text string you supplied it with in the first argument (“Excel”) and extracts the number of characters from the start of the string based on the second argument (“2”).

Purpose

This function is similar to the Excel MID function in Excel and comes in handy when you want to quickly extract certain information from a text string.

And when combined with functions like SEARCH, SUM, COUNTIF, or LEN, you can accomplish more complex tasks like extracting parts of names, dates, email addresses, or URLs.

Syntax

=LEFT(text,[num_chars])

Arguments

• text – the text string you want to extract characters from.
• num_chars – the number of characters you want the function to extract from the specified text string.

Return Value

The Excel LEFT functions return the extracted substring (characters) as text/string values.

Usage Notes

1. The second argument (num_chars) is optional with the default value set to “1,” meaning if you decide to leave it out, the function extracts the first character from a text string.

2. When the num_chars argument is greater than the overall number of characters in your string, the function returns the entire string.

3. If num_chars equals zero, the LEFT function returns an empty string.

4. When num_chars if less than zero, that triggers the #VALUE error.

5. Each whitespace counts as one character, so it’s better to eliminate any leading spaces in your data set to prevent the function from extracting the wrong characters.

6. The function can’t be applied to dates. This has to do with the way date values are stored in Excel as special numeric values which get converted to dates. When applied to dates, the function extracts the internally stored numerical value tied to a given date.

Examples

To show you how to use the LEFT function, let’s pick one phrase, “Bird is the word,” and tweak it in various ways.

1. The LEFT function below extracts the first four characters from the string.

=LEFT("Bird is the word", 4) // Output: Bird

2. This variation pulls the first seven characters from the string. Note how the white space between the words “bird” and “is” gets treated as a separate character.

=LEFT("Bird is the word", 7) // Output: Bird is

3. Combined with the SEARCH function, the formula below uses the first occurrence of white space in the string as a filtering tool to dynamically extract the first word from any string.

Notice how the formula does its work no matter what string you supply it with:

=LEFT("Bird is the word", SEARCH(" ", "Bird is the word")-1) // Output: Bird
=LEFT("Secret agent man", SEARCH(" ", "Secret agent man")-1) // Output: Secret

4. This version of the function pulls the entire string since the second argument exceeds the total number of characters in our string.

=LEFT("Bird is the word", 100) // Output: Bird is the word

5. Finally, the last example of the LEFT function shows how you can convert numerical values into their text counterpart.

=LEFT(2021,4) // Output: 2021 as text/string values

Related Article: How to Use the Excel LARGE Function

How to Use the LEFT Function in Excel VBA

Did you know that the LEFT function also works in Excel VBA? 

Here’s a quick example of how to use the LEFT function in VBA. This simple VBA code pulls the first four characters from the good ol’ phrase “Bird is the word” stored in cell A3.

Sub VBA_LEFT_Function()
LeftValue = Left(Range("A3"), 4)
Range("B3") = LeftValue
End Sub

To adjust the code to your data, do the following:

1. Swap out “Range(“A3”)” with the location of the worksheet cell where the original text string is stored. 
2. Then, change “Range(“B3”)’ to the cell where you want the output of the LEFT function to be extracted into. That’s it!