Left formula in excel

The left function is used to extract the characters from the left side of the string. The specified characters can be in the form of numbers, characters, or symbols. It also includes spaces between two or more characters. We can specify the number of characters we want to extract from the left of the given data element. We can also say that LEFT() function returns the last characters of the string.

It is given by:

LEFT(text, Number_of_characters)

Where,

Text refers to the specified cell from which we want to extract the elements.

Number_of_characters refers to the number of characters we want to extract from the given data in excel.

Note: Unlike the MID() function, it does not require the start_position parameter in the function. It is because the LEFT() function starts from the left end of the string by default.

The main difference between the LEFT() and the RIGHT() function is the extraction of characters from the specific end of the string. The extraction using the LEFT() function starts from the left end of the string towards the right, while using the RIGHT() function, it is from the right end towards left.

For example,

Data string = abcd123bdf

Here, we want to extract the code in the form of characters present from the left side of each string. So, we will use the LEFT() function.

The syntax for the above string will be:

=LEFT(abcd123bdf, 5)

Excel will start extracting from the left end of the given string. The extraction will start from the first character of the string (a) and counts till the 5th character (1). Thus, the output will be:

Output: abcd1

We will also discuss LEFTB() function, another part of the LEFT() function that counts double-byte characters as 2. While the LEFT() function is used for the languages that use the single-byte character. It will be discussed later in the topic.

Shortcut Formula

We can also access the LEFT() formula using the shortcut method. Let’s discuss.

  1. Select any cell and click on the Formulas icon present above the formula bar in excel, as shown below:
    Left formula in excel
  2. Click on the Text icon. A drop-down list will appear. Select the ‘LOWER’ option from the drop-down list, as shown below:
    Left formula in excel
  3. A dialogue box will appear. Specify the text and the number of characters we want to extract starting from the left side of the text, as shown below:
    Left formula in excel
  4. Click on the ‘OK’ button at the bottom of the dialogue box. The output will appear on the selected cell.

Important points

  • If the number_of_characters specified are greater than the length of the text, excel will return characters upto the end of the text.
    For example,
    Text: 12346543
    Syntax: LEFT(12346543, 20)
    Here, excel will return 12346543, i.e., characters upto the end of the end starting from the left end to the right end.
  • If the number_of_characters are negative, excel will return ‘#VALUE!’
    For example,
    Text: YTR45321
    Syntax: LEFT(YTR45321, -5)
    Here, excel will return ‘#VALUE!’

Let’s first consider some examples of the excel LEFT() function.

Examples

Example 1: To extract the code number from the given string.

Consider the below steps:

  1. Click on the first cell of the extracted word column and type =LEFT(A3,3) as shown below:
    Left formula in excel
  2. Press Enter. The desired result will appear on the selected cell.
  3. Drag and drop-down it to the last column, i.e. upto the fifth cell of the column. The LEFT formula will be applied automatically to the rest of the cells of the column.
    Left formula in excel

It is shown below:

Left formula in excel

It means that LEFT() function will start extraction the elements from the left side (1st element) and counts till the third element y moving from left to right. Thus, the value returned will be the first three characters.

The LEFT() function on each cell will work as:

=LEFT(10023456, 3)

Value returned: 100

=LEFT(10123456, 3)

Value returned: 101

=LEFT(10223456, 3)

Value returned: 102

=LEFT(10323456, 3)

Value returned: 103

=LEFT(10423456, 3)

Value returned: 104

=LEFT(10523456, 3)

Value returned: 105

Example 2: To extract the code from the given string.

Consider the below steps:

  1. Click on the first cell of the Code column and type =LEFT(A2,4), as shown below:
    Left formula in excel
  2. Press Enter. The desired result will appear on the selected cell.
  3. Drag and drop-down it to the last column, i.e. upto the fifth cell of the column. The LEFT formula will be applied automatically to the rest of the cells of the column. It is shown below:

Left formula in excel

Similarly, we can extract the desired number of characters from the given string.

Example 3: To extract the country code from the given list of phone numbers.

Here, a list of phone numbers of various countries is given. We are required to extract the country code which is present at the starting of every phone number. The code can be of two digits, three digits, or more. So, here we will use the SEARCH() function with the LEFT() function.

SEARCH(): It helps us to find the required content in the excel sheet.

The syntax is given by:

LEFT(string, SEARCH(character, string) -1)

The number of characters preceding the specified character in the function will be returned. Here, 1 is the default size.

Consider the below steps:

  1. Click on the first cell of the Code column and type ‘ =LEFT(A2, SEARCH(“-“,A2)-1)’ as shown below:
    Left formula in excel
  2. Press Enter. The desired result will appear on the selected cell, as shown below:
    Left formula in excel
  3. Drag and drop-down it to the last column, i.e. upto the fifth cell of the column. The LEFT formula will be applied automatically to the rest of the cells of the column. It is shown below:
    Left formula in excel

The country code preceding the ‘-‘symbol will be returned at the selected cell irrespective of the digits. Similarly, we can use the same concept to extract the characters in various ways.

Example 4: How to remove the last N characters from the given string

We will use the LEN() function with the LEFT() function to extract the last N characters from the given string.

LEN(): It returns the length of the specified string.

The syntax is given by:

LEFT(string, LEN(string) – number_of_characters_to_remove)

For example,

LEFT(D2, LEN(D2)-6)

Where,

D2 = abvd 1-34a

The function will remove the last six characters from the string, i.e., one alphabet, three numbers, one hyphen, and one extra space. The value returned will be abvd.

Let’s discuss it with an example.

Consider the below steps:

  1. Click on the first cell of the Result column and type ‘ =LEFT(A2, LEN(A2)-7),’ as shown below:
    Left formula in excel
    The function will remove last seven characters, including space and hyphen from the given string.
  2. Press Enter. The desired result will appear on the selected cell, as shown below:
    Left formula in excel
  3. Drag and drop-down it to the last column, i.e. upto the last cell of the column.

Left formula in excel

LEFTB() function in excel

Let’s have a quick look at the LEFTB() function in excel to eliminate the confusion between the LEFT() function and LEFTB() function.

The LEFTB() function considers the double byte character as 2, when we enable the editing a language that supports DBCS (Double-Byte Character Set) and set it as a default language. Otherwise, it considers 1. In the case of LEFT() function, it considers every single byte character as 1. It means that LEFTB() works as the same like LEFT() function instead of the DBCS when set as the default language.

The double-byte languages are Chinese, Korean, and Japanese. Such languages are difficult to represent in the single-quote.

It is given by:

LEFT(text, start position, Number_of_bytes)

Where,

Text refers to the specified cell from which we want to extract the elements.

Start_position refers to the position from where we want to start extracting.

Number_of_bytes refers to the number of characters we want to extract from the given data in excel.

The output and the conditions of the LEFTB() are similar to that of LEFT() function.

Important points

  1. The number of bytes should be greater than or equal to 0.
  2. If the numbers of bytes are excluded, it is assumed as 1.
  3. If the specified numbers of bytes are greater than the length of the string, it will return the full string.