How to use LEFT function in Excel (4 Examples)

Excel provides several text functions to help you perform your desired tasks easily and swiftly. Today we are going to show you how to use a text function called: LEFT. For this session, we are using Excel 2019, feel free to use yours (at least 2003).

Overview- Excel LEFT Function

Practice Workbook

You are welcome to download the practice workbook from the link below.

LEFT Function

1. Basics of LEFT

The LEFT function is categorized under the TEXT functions in Excel. This function returns a specified number of characters from the start of the provided text string.

Syntax - Excel LEFT Function

Summary

Returns a specified number of characters from the start of a text string.

Syntax

LEFT (text, [num_chars])

Arguments

Argument Required/Optional Description
text Required The text string that contains the characters to extract
num_chars Optional The number of characters to extract. By default 1

Versions

Workable from Excel 2003.

2. Uses of LEFT

I. Extract String using LEFT

From the description of the LEFT function, you might have understood that this function will help you extract a string from the beginning (left) of a text. All you need to do is to set the text and the number of characters you want.

For example, we have a dataset of several employees with their respective ids and names.

Example dataset - Excel LEFT Function

Here from the Employee Id-Name column, we will extract the id portion. The ID number consists of 4 characters. So our formula will be

=LEFT(B4,4)

B4 is the Cell Reference for the first row of the Employee Id-Name column.

Basic extraction - Excel LEFT Function

The formula provided the desired string we were looking for. Similar formula (changes in the Cell Reference) will provide the Employee ID for the rest of the rows.

Basic extraction autofill- Excel LEFT Function

II. Extract Text up to a Specific character

The LEFT function will help you fetch a text searching for any specific character.

To show you examples, we have brought a dataset of a few email addresses. We will find the user name from the email.

Specific character search data- Excel LEFT Function

Since we need to look for a specific character, our formula will be a nested one. We will use the SEARCH function along with LEFT.

The SEARCH function returns the location of one text string inside another.

Our formula for the first row will be

=LEFT(B4,SEARCH("@",B4)-1)

Specific character search result- Excel LEFT Function

Here we have searched for @, the SEARCH function would return the position of @. We wanted up to @ so we subtracted 1 from the position number. This provided the user name.

Specific character search autofill- Excel LEFT Function

III. Remove Characters from the End of a String

Using LEFT we can extract the desired text by removing the characters from the end of the string.

Our example dataset has the names of a few athletes. We are set to find their first name from the full name.

Remove last strings data - Excel LEFT Function

To remove the characters first we need to find the length of the string, the LEN function will help us regarding that.

The generic formula will be

=LEFT(text,LEN(text)-num_characters_remove) 

For our example dataset the formula might be similar to the below one

=LEFT(B4,LEN(B4)-7)

We want 7 characters to be eradicated from the text stored in B4. 

Remove last strings result - Excel LEFT Function

The LEN function provided the total number of characters in a string. The LEFT formula in excel subtracts the number of unwanted characters from the total length, and the LEFT function in excel returns the remaining characters.

A similar formula will provide the result for the rest of the texts.

Remove last strings autofill - Excel LEFT Function

IV. Force to Return Numbers

We can fetch numbers from text. The string we store, usually are in text format. We can fetch the digit value from the string and convert them to a number.

Our example dataset contains several IDs for employees.

Value conversion data - Excel LEFT Function

The first 3 characters are digits. We will fetch these 3 characters as a number. The VALUE function will be useful for our operation.

VALUE converts text that appears in a recognized format into a numeric value. Our formula will be

=VALUE(LEFT(B4,3))

Value conversion result - Excel LEFT Function

We have found the 3 characters from the string and converted them into numeric.

Value conversion autofill - Excel LEFT Function

3. Quick Notes

  1. If we provide a value less than 0 into the num_chars field

negative number - Excel LEFT Function

Then it will provide #VALUE! error.

negative number output - Excel LEFT Function

  1. For dates, you may not find the exact value you are wanting.

date value - Excel LEFT Function

From a date, if you want to find the day portion, you may write LEFT with 2 in the num_chars field.

formula for date value - Excel LEFT Function

But will find another value, not the day value of the provided date.

Result for formula with date - Excel LEFT Function

Excel stores dates as sequential serial numbers. 1 January 1900 is the 1st date, and its serial number is 1. From there on every date has its subsequent serial number. The functions convert dates to their respective serial number before doing the operations.

If you set the cell containing the date into General you will find the serial number. The formula will extract from that serial number.

Date stores value - Excel LEFT Function

  1. You can provide the text value directly into the function.

Direct input - Excel LEFT Function

You will find the result as expected

Direct input result - Excel LEFT Function

Conclusion

That’s all for today. We have tried showing how you can use the LEFT function. You can use the function to extract text from the beginning of a string as well as several advanced operations can be performed. Hope you will find this helpful.

Feel free to comment if anything seems difficult to understand. Let us know any of your LEFT function-related scenarios where you have stuck, we are ready to help.


Further Readings:

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo