How to Use the Excel LEFT Function (Plus Examples)

By Indeed Editorial Team

Published 5 May 2022

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

The Excel LEFT function can help you complete a variety of tasks when you're working with a spreadsheet. You can use this function to help you isolate text from the left side of a string. Learning how to use this function may help you complete your work tasks more effectively, allowing you to have a more organised spreadsheet and time to work on other projects. In this article, we discuss what the Excel LEFT function is, describe how to use the function in five steps, list its various usages and provide some examples you can use as a reference.

What is the Excel LEFT function?

The Excel LEFT function is for informing Excel to return one or more characters from the left of a text string. A text string is a group of characters in Excel that represent data in your spreadsheet. The purpose of using this function is for Excel to extract letters, numbers or both values from a text string. For example, if you type in the word "plant" and the number "two" into the function, it returns the values "pl" and displays them in your chosen cell.

You may find this function useful when you want fewer characters and more abbreviations present in your spreadsheet. This may help you read and analyse a spreadsheet easier as it reduces the amount of data visible, which is especially beneficial for larger data sets.

Related: How to Become a Computer Programmer in 5 Simple Steps

Syntax of the LEFT function

Syntax in Excel refers to the layout or order of a function and its arguments. Arguments are values in a function used to perform a calculation. Here's what the syntax for the LEFT function commonly looks like:

  • =LEFT(text, [num_chars])

The arguments in this syntax are "text" and "num_chars". You can input specific text into the "text" portion of the formula. The numbers you use in "num_chars" represent how many characters Excel extracts from a text string. If you don't include a number, Excel uses one by default. It's important to remember that the figure entered into "num_chars" is required to be greater or equal to zero to work properly. If the number you choose exceeds the length of a text string, the function outputs the entire text string.

Related: How to Become a Forensic Computer Analyst (With Skills)

How to use the LEFT function in Excel

Here are some steps you can follow to use the LEFT function in Excel effectively:

1. Open the program and select your cell

Launch Excel, then open the file you want to work on. When you have your spreadsheet open, click on the cell you want to input the LEFT function into. When you select a cell, Excel creates a thick border around it, so you can easily see which cell you've selected.

Related: How Much Does a Computer System Analyst Make? (With Career Steps)

2. Use the "Formulas" tab

Navigate your cursor to the "Formulas" tab. You can locate this tab at the top of your screen in Excel's toolbar ribbon. Click on it and select the "Text" drop-down box. This button is in the third column from the left of the choices in this tab. From the drop-down box, click on the "LEFT" option.

3. Enter specifications

When you select the "LEFT" function from the drop-down box, Excel generates a dialogue box labelled "The Functions Arguments", which is where you can choose the specifications you want. Under the "Text" field, click on the cell that has the characters you want to extract. Then, under the "num_chars" field, enter the number of characters you want the function to extract.

Related: What Does a Computer Programmer Do? (And How to Become One)

4. Execute the function

Before clicking on the "OK" button, consider reviewing the information you've entered. It's important to ensure that you've entered all the values and characters in the fields correctly to get your desired outcome. When you're satisfied, click on the "OK" button. This closes the dialogue box and extracts the defined number of characters from the left of the chosen text string and places it in the cell you selected at the beginning of the process.

Usages of the LEFT function

Here are some usages of the LEFT function for reference:

Removing characters from the right side of a text string

You can use this function to remove characters from the right side of specific cells. For example, you may have a long list of numbers with several digits, but you're only interested in the first three digits. You can use the LEFT function to isolate the first three digits of all the numbers.

Separating a text string

You may find it helpful to use the LEFT and RIGHT functions for separating a text string based on where a number first appears. For example, you may have names and phone numbers written together in individual cells. You can separate them into two different cells using various formulas, including the LEFT function. Input "=MIN(FIND({0,1,2,3,4,5,6,7,8,9},[cell reference]&"0123456789"))" to find where in a text string the first number appears. For example, if you have a cell with "Mary57492049", the formula outputs "5" because a number appears for the first time in the fifth digit of this text string.

You can now combine this with LEFT and RIGHT functions to separate the text string. If the text string is in cell "A1" and the formula for identifying the position of the first digit is in cell "B1", you can input "=LEFT(A1,B1-1)" in cell "C1" and ""=Right(A1,B1-1)". Performing these steps outputs "Mary" in cell "C1" and "57492049" in cell "D1".

Removing file extensions from filenames

You can use the LEFT function when you want to remove file extensions from a list of filenames. Here's what the generic formula looks like:

=LEFT([cell reference],FIND (".",[cell reference])-1)

You can input the cell with the filename into the "cell reference" sections of the formula. This formula extracts only the characters to the left of the first period in a filename. So if you have a filename "document.pdf", the formula outputs "document" only. This formula specifically looks for the first period to use as a reference when extracting characters, meaning that it deletes all file extensions if there are multiple.

Extracting data left of a common character

Another usage of the LEFT function is combining it with the search function. Here's an example of what the combined formula can look like:

=LEFT([cell reference],SEARCH("[character]",[cell reference])-1)

You can use this function if you have data that has two distinct sections separated by a common character. For example, you may have a list of names that include both first and last names that are separated by a comma in column "A". If the first names are on the left of the comma, you can use this formula to extract them by inputting "=LEFT(A1,SEARCH(",",A1)-1)" in cell "B1" and dragging the formula down the column "B" to perform the same function on the rest of the names.

Separating text strings based on case sensitive information

Combining the LEFT function with the FIND function is similar to combining it with the SEARCH function, but the FIND function is case sensitive. Case sensitive means that the SEARCH function ignores capitalisation either in the formula or in the text string, whereas the FIND function doesn't. This means that you may use the "Find" function if the characters you're using as the reference point for a LEFT function are case sensitive.

For example, if you have a text string of "abcC" in cell "A1" and you input "=LEFT(A1,SEARCH("C",A1)-1)", it outputs "ab" as the SEARCH function ignores that you're looking for a capitalised C. If you use the FIND function instead, by inputting "=LEFT(A1,FIND("C",A1)-1)", it outputs "abc".

LEFT function examples

There are several results you may experience when using this function in Excel. Depending on the characters you enter, can change the result of your returned value. It's important to pay attention to the number of characters and ensure they're correct before executing this function. Here are some examples of the LEFT function being used:

Returning three characters

Here are some examples of using the function to return the first three characters of a text string:

  • =LEFT(chair,3) = cha

  • =LEFT(bed,3) = bed

  • =LEFT(984561,3) = 984

  • =LEFT(cx837,3) = cx8

Leaving the number value blank

Here are examples of when you don't define several characters to extract:

  • =LEFT(circle) = c

  • =LEFT(square) = s

  • =LEFT(triangle) = t

  • =LEFT(rectangle) = r

Exceeding the text string limit

Here are some examples of choosing several characters that exceed the text string:

  • =LEFT(apple,6) = apple

  • =LEFT(orange,8) = orange

  • =LEFT(grape,12) = grape

  • =LEFT(cherry,63) = cherry

Please note that none of the companies, institutions or organisations mentioned in this article are affiliated with Indeed.

Explore more articles