Technology

What is the LEFT function? How to use the LEFT function in Excel, Google Sheets most effectively

LEFT function is a useful tool for extracting data. The following article will guide you on how to use the LEFT function in Excel and Google Sheets.

Administrator

31 October, 2023

332

What is the LEFT function? The LEFT function formula

The LEFT function is a basic function in Excel used to extract one or more first characters from a text string. The LEFT function formula is as follows:

=LEFT(text; [num_chars])

Where:

text: Is the text to extract.
num_chars: Is the number of characters to extract. If not specified, Excel defaults to 1.

How to use the LEFT function:
Step 1: Open a new Excel worksheet or open a worksheet that contains the data you want to apply the LEFT function to.

Step 2: Select an empty cell where you want to display the result of the LEFT function.

Step 3: Enter the formula =LEFT(B2;3) into that cell.

Step 4: Press Enter to get the result.

Advanced usage of the LEFT function in Excel

Combining with the SEARCH function

The LEFT function combined with the SEARCH function is used to extract a text string based on the position of a specific character in that text string. The formula for combining the LEFT function with the SEARCH function is as follows:
=LEFT(text; SEARCH(find_text; text) – 1)
Where:

text: Is the text to extract.
find_text: Is the character or text string to search for.

Here are some specific examples of using the LEFT function combined with the SEARCH function:
We have the following worksheet, the requirement is to extract the “Last Name” from the “Full Name” field.

Step 1: In this step, we will use the LEFT function combined with the SEARCH function to extract the “Last Name” from cell B2.
Formula:
=LEFT(B2; SEARCH(” “; B2)-1)
Explanation:

B2: is the cell reference to the text string to be extracted.
SEARCH(” “; B2): returns the position of the space character in cell B3.
-1: to subtract 1 character, because the LEFT function extracts the text string from the beginning of the text string to the position of the space character, minus 1 character.

Step 2: Press Enter.

Combining with the VALUE function

The LEFT function combined with the VALUE function is used to extract one or more first characters from a text string and convert that text string into a numeric value. The formula for combining the LEFT function with the VALUE function is as follows:

=VALUE(LEFT(text, [num_chars]))
Where:

text: Is the text to extract.
num_chars: Is the number of characters to extract. If not specified, Excel defaults to 1.

Here are some specific examples of using the LEFT function combined with the VALUE function:
We have the following worksheet, the requirement is to extract the first 2 numbers from the “Student ID” field.

Step 1: Select any cell and enter the formula =VALUE(LEFT(D2,2)).

Explanation:

D2: Is the cell reference to the text string to be extracted.
LEFT(D2,2): Extracts the first 2 characters from cell D3.
VALUE: Converts the text string to a numeric value.

Step 2: Press Enter.

Combining with the LEN function

The LEFT function combined with the LEN function is used to extract one or more first characters from a text string, based on the position of a specific character in that text string. The formula for combining the LEFT function with the LEN function is as follows:
=LEFT(text, SEARCH(find_text, text) – 1)
Where:

text: Is the text to extract.
find_text: Is the character or text string to search for.

Here are some specific examples of using the LEFT function combined with the LEN function:

We have the following worksheet, the requirement is to remove the last 3 characters.

Step 1: Select the data cell.

Step 2: To remove the last 3 characters in a text string, enter the formula:
=LEFT(B2; LEN(B2) – 3)
Explanation:

B2: Is the cell reference to the text string to be extracted.
LEN(B2): Returns the length of the text string in cell B3.
-3: To remove 3 characters from the end.

Step 3: The result is returned.

How to use the LEFT function in Google Sheets

The steps to use the LEFT function in Google Sheets are similar to Excel, so we won’t repeat the basic steps here. Instead, let’s explore another usage of the LEFT function in Google Sheets with the combination of the FIND function.

The LEFT function in Google Sheets can be used to extract one or more first characters from a text string. However, the LEFT function can only extract a fixed number of characters from the beginning of the text string. In cases where you need to extract a certain number of characters from the beginning of the text string, but that number of characters varies depending on each cell, you can use the LEFT function combined with the FIND function.

The formula for combining the LEFT function with the FIND function in Google Sheets is as follows:
=LEFT(text, FIND(find_text, text) – 1)
Where:

text: Is the text to extract.
find_text: Is the character or text string to search for.

Here are some specific examples of using the LEFT function combined with the FIND function:

We have the following worksheet, the requirement is to remove the last 3 characters.

Step 1: Select the data cell.

Step 2: Enter the formula =LEFT(B2;FIND(” “;B2)-1)
Explanation:

text: Is cell B2.
find_text: Is the space character.

Step 3: The result is returned.

Conclusion

In this article, FPT Shop has guided you on how to use the LEFT function in Excel, including basic steps and combining methods. Hopefully, the article has helped you understand the LEFT function and apply it to real-life cases.