Excel LEFT Function – How To Use

The LEFT function is one of the many common and widely used text/string functions offered by Microsoft Excel.

The LEFT function enables you to extract a predetermined number of characters from a text string beginning on the left side. You just have to provide the input text or the reference cell to the function and the number of characters you wish to retrieve from the beginning of the text.

In this post we will take a closer look at the LEFT function and explore the value it can provide when it comes to text manipulation in Excel.

Excel LEFT Function

Syntax

The syntax of the LEFT function is mentioned below:

=LEFT(text, [num_chars])

Arguments:

'text' – This is a required argument that may be supplied with either the text string in double quotes or a cell reference to the text.

'num_chars' – This is an optional argument with default as 1. It sets the number of characters you wish to extract from left to right.

Important Characteristics of the LEFT Function

  • The LEFT function is a text function, meaning the result will always be a text string. Even if the input value is numerical, the return value will be in text format.
  • If num_chars exceeds the total text length, it will return the complete text.
  • If the text argument is provided as a text string without double quotes, it returns a #NAME error.
  • If the value in the num_chars argument is non-numeric, the function yields a #VALUE error.
  • If num_chars is not an integer, the function will round off the number to the lower integer and return the applicable characters.
  • If num_chars is assigned a negative number, the LEFT function will return a #VALUE! error.

Examples of LEFT Function

Let's try to understand the LEFT function using examples. You can always try all the examples on your own in a spreadsheet to get a better understanding.

Example 1 – Basic Functionality

Here in this dataset, we have taken 5 different scenarios of using the basic functionality of the LEFT function. First, let's see how the function behaves with text, special characters, space, and numbers.

Examples of LEFT Function

In case 1, cell C3 contains the text 'Simple Sentence'. We want to extract the first three letters of the text. So, the simple formula goes like:

=LEFT(C3,3)

We use the formula in E3 by giving the cell reference C3 as the text parameter and 3 as num_char as we want the first three characters. First three characters from left to right would be 'Sim,' hence the return value in cell E3.

Let's find out how the LEFT function behaves with the special characters in case 2. The input value contains a double quote, dollar and exclamation marks in the text string in cell C4. We are trying to extract the first 6 characters of the text; therefore, the formula will be as follows:

=LEFT(C4,6)

The input data is "$pec!al Character" where double quote, dollar and exclamation mark all are counted as characters. The result is "$pec!, which is the first 6 characters from left. This simply tells you that all special characters, even double quotes and spaces, are counted when using the LEFT function.

Try the same thing with numbers. Here with case 3, in cell C5 the input parameter is 123467. To extract 4 characters from the beginning, The formula will be as follows:

=LEFT(C5,4)

By now, you know the result. It will be 1234. It should be noted here that the return value in cell E5 will be in text format rather than in numerical format. We will discuss this case in detail in the below section.

What will happen if you omit the second parameter, num_chars, in the LEFT function? As mentioned earlier, the default value for this parameter is 1. In case 4, we can use this formula:

=LEFT(C6)

The input data here is in C6, which is 'Default Value'. As we have not mentioned the second parameter, the result will be one character – D, as that is the first character from the left.

Example 2 – Extracting Substring Before a Particular Character

Suppose you want to extract the first name from a list of full names, find the country code from a list of phone numbers, or determine the state code from a car number. You can use the LEFT function, but now the issue is that since the number of characters in each case may vary, you would not be able to use a fixed numerical value in the num_char argument.

But, if there is a space or special character in the data that is separating the substring you want from the remainder text, we can easily find the position of the special character using either the SEARCH or FIND function and then use it with the LEFT function to return the values before that particular character.

Counting from the left, the SEARCH function returns the position of a character or text string. In this case, we have the addresses of people, which are a combination of house numbers and street names separated by a space. We want to obtain the house numbers alone in a separate column. This can be achieved using the LEFT function combined with the SEARCH function.

Extracting Substring Before a Particular Character

The input here is the full address in B2, and the position of the space is found and returned using the following formula:

=SEARCH(" ",B2)

Now, enter the SEARCH function in the LEFT formula in the num_chars argument. By doing this, you are telling the LEFT function the position of space in the address. The final formula to be used will be:

=LEFT(B2,SEARCH(" ",B2))

This way, Excel will determine the position of the space character and return the characters before it.

Let's take another example for better understanding.

Example 3 – LEFT Function with SEARCH Function

The data set we use in this example consists of product names in column B, which are shoe names with their brand names. To check which brand is more popular in shoes these days, we wish to segregate brand names from the product names. Lucky for us, the brand name is followed by a hyphen '-' and then the product name.

This time, instead of a space character, we look for the dash (-) when using the LEFT function. In cell B2, the input value is 'SKETCHERS-Go Run Consistent', and we want to extract the data before '-'. Now, the formula becomes:

=LEFT(B2,SEARCH("-",B2))

LEFT Function with SEARCH Function

SEARCH function finds the position of '-', and the LEFT function extracts the characters before it. The desired result is in cell C2 which is SKETCHERS-.

Suppose you want to improve the formula and remove the '-' from the earlier result. You can reduce 1 more character from the LEFT formula result. So simply using it like it is mentioned below. (the "-1" argument doesn't belong to the SEARCH function but the LEFT function as shown in the screenshot below:

LEFT Function with SEARCH Function

By telling the LEFT function to subtract one more character from the result when the '-' is found. This is an easy inclusion in the formula and the complete formula becomes:

=LEFT(B2,SEARCH("-",B2)-1)

To have the LEFT function return one character less from the right, the num_chars argument has also been entered as "-1". The effect can be seen below as the brand names are returned without hyphens:

Now you have clean data without the special character (-).

You can now easily extract a substring from data before a special character. Now, what if you want to remove certain characters from the end of the string and extract the rest.

We are left with clean data without the special character (-). You can now easily extract a substring from the data before a special character. But what if you only want to remove certain characters from the end of the string?

Example 4 – How to remove last few characters from string

To remove the last N characters from a string, we can try to use the LEN function with the LEFT function.

This is how it works: the LEN function determines the number of characters in a string and returns a numerical value. We can then subtract the required number of characters from the total length and return the remaining characters using the LEFT function.

In this example data set, we have a list of Employee IDs in column B that are a combination of department names followed by unique 5-digit IDs.

How to remove last few characters from string

We wish to know the department of each employee and, therefore, want to extract the department name from the employee ID. This cannot be done using the traditional usage of the LEFT function since the number of characters for each department is different.

Also, we cannot use the SEARCH or FIND function since there is no space or special character between the department name and employee ID. Hence, we use the LEFT function in combination with the LEN function.

The input string – Employee ID is in cell B2. The number of characters can be calculated in the string using the LEN function, which in this case would return a numerical value of 11. Then upon using this in the num_chars argument of the LEFT function and subtracting the last 5 characters (as we know that the employee ID is fixed with 5 characters), we get the desired result.

The final formula goes as follows:

=LEFT(B2,LEN(B2)-5)

How to remove last few characters from string

You have your department names as required in column C.

Let's mix it up even more and use the LEFT function with dates. Will it behave in the same manner?

Let's find out.

LEFT Function with Dates

When working with dates in Excel, they are only displayed as dates due to cell formatting. Otherwise, they are actually serial numbers in Excel. If we try to use the LEFT function on a date, it will return the first few characters of the number corresponding to that date. For Excel, 01 January 1900 is 1, and then it keeps adding as we add a day.

For instance, 01 July 2022 is represented by the number 44743. Using the LEFT function to retrieve two characters from cell B3 holding the date 01-07-2022 will produce the value "44," as seen in C2.

LEFT Function with Dates

Now there is one more way to utilize the LEFT function with dates. It is to insert the date directly as a text argument. So, we use the following formula:

=LEFT("01 July 2022",2)

In this case since the date is supplied as text so the LEFT function will simply extract the first two characters, i.e. the date 01, from the input string.

LEFT Function with Dates

For extracting Years, Month or Day from Dates in Excel we have better options in the form of YEAR, MONTH and DAY functions.

Forcing LEFT Function to Return Numbers

As we already know by now, the LEFT function is a text function; therefore, any value it returns will be in the text format. In the earlier example of using the LEFT function with '123467' as the input string, the number of desired characters is returned but as text. This means you may not be able to use it for calculations. Let's try it with an example.

In the example below, we are trying to use the LEFT function with numbers. We wish to multiply 900 with 15 in cell B2 and then extract the first 3 characters from the result. So, the formula used is as follows:

=LEFT(900*15,3)

Forcing LEFT Function to Return Numbers

As per basic mathematical calculation, 900*15 is 13500. Extracting the first 3 characters from it gives the result 135.

The important observation here is that the result of our formula is returned as a text value by default. Excel aligns numbers to the right and text to the left, also seen in the example below.

Forcing LEFT Function to Return Numbers

By this logic, the result in cell B2 should be aligned to the right. But, as you can notice, it is left aligned indicating the text format. Another way to check whether the value in cell B2 is text or number is by using the ISNUMBER function.

Forcing LEFT Function to Return Numbers

As you can see, when we use the ISNUMBER function to check whether the value in B2 is a number or not., the return value is FALSE.

So, how to convert this text format to number format? The solution is easy.

Use the VALUE function. The VALUE function converts text representing a number into a number. Using the VALUE function with the LEFT function extracts a numerical value from the left as text which then gets converted to a number. The formula used will be as follows:

=VALUE(LEFT(900*15,3))

Forcing LEFT Function to Return Numbers

As shown in the example below, we have used the formula in cell B2. To confirm the datatype of the returned value we have again used the ISNUMBER function which returns TRUE. Also, note that the result values in column B are now right-aligned.

Forcing LEFT Function to Return Numbers

LEFT Function vs RIGHT Function vs MID Function

You have now comprehensively understood how to use the LEFT function in different scenarios. What if you wish to extract the characters from right to left or from the middle of a string? You can do so by using the RIGHT function and MID function respectively.

Let's compare the three functions using an example for a better understanding. Here we have taken Input Text as 'Smart, Easy & Effective', in cell A2. We are using the LEFT function in cell B2, MID function in cell C2, and RIGHT function in cell D2.

LEFT Function vs RIGHT Function vs MID Function

For the LEFT function, we are trying to extract the first word 'Smart' from the left which is 5 characters long. As we know to do this we are using the following formula:

=LEFT(A2,5)

Excel MID function returns the desired number of characters from the middle of the string. We want to extract the word 'Easy' from the text. If we want to use the MID function, the formula used will be as follows:

=MID(A2,8,4)

There is an additional parameter, the second one, which is the starting location – 8. As the first word is 'Smart', which is 5 characters long followed by a comma and a space, this parameter becomes 8.

The RIGHT function works exactly like the LEFT function, just from the opposite direction. We are trying to extract the last word from the input sentence. Formula used is:

=RIGHT(A2,9)

The result is the last word 'Effective' which is 9 characters long.

That wraps up the LEFT function. Hope these examples gave you a good insight into how you can use the LEFT function in your worksheets. Spend a little time with these formulas, and you will champion them in no time.

Compiled by - Content Studio

Thanks for reading. If you have found this article helpful show your love by sharing it with your friends & colleagues. All the tutorials on the Excel Trick are produced, reviewed, and fact-checked by a team of experts. You can check out our team here.