When text is copied or imported into Excel, unwanted garbage characters are sometimes included with the good data. Sometimes only part of the text data in the cell is needed. For instances like these, use the LEFT function to remove unwanted data when the good data is on the left side of the unwanted characters in the cell.
The instructions in this article apply to Excel 2019, 2016, 2013, 2010, 2007; Excel for Microsoft 365, Excel Online, Excel for Mac, Excel for iPad, Excel for iPhone, and Excel for Android.
Excel LEFT and LEFTB Function Syntax
The LEFT and LEFTB functions perform similar operations but differ in the languages they support. Follow the guidelines below and pick the function that best supports your language.
- LEFT is for languages that use the single-byte character set. This group includes English and all European languages.
- LEFTB is for languages that use the double-byte character set. This includes Japanese, Chinese (Simplified), Chinese (Traditional), and Korean.
In Excel, a function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments. The syntax for the LEFT function is:
The syntax for the LEFTB function is:
The function's arguments tell Excel which data to use in the function and the length of the string to be extracted.
- Text (required for LEFT and LEFTB) refers to the entry that contains the desired data. This argument is either a cell reference to the location of the data in the worksheet or the actual text enclosed in quotation marks.
- Num_chars (optional for LEFT) specifies the number of characters on the left of the string argument to be retained. All other characters are removed.
- Num_bytes (optional for LEFTB) specifies the number of characters on the left of the string argument to be retained in bytes. All other characters are removed.
Important Notes About LEFT Function
When entering the LEFT function, keep these points in mind:
- If Num_chars or Num_bytes is omitted, the default value of 1 character is displayed by the function.
- If Num_chars or Num_bytes is greater than the length of the text, the function returns the entire text string.
- If the value of the Num_chars or the Num_bytes argument is negative, the function returns the #VALUE! error value.
- If the value of the Num_chars or the Num_bytes argument references a blank cell or is equal to zero, the function returns a blank cell.
Excel LEFT Function Example
There are several ways to use the LEFT function to extract a specific number of characters from a text string, including entering the data directly as arguments for the function and entering cell references for both arguments.
It is best to enter cell references for arguments rather than the actual data. This example lists the steps to enter the LEFT function and its arguments into cell B3 to extract the word Widget from the text string in cell A3.
Enter the LEFT Function
Options for entering the function and its arguments into cell B3 include:
- Typing the complete function into the appropriate cell.
- Using Excel's Function Arguments dialog box (or the Formula Builder in Excel for Mac).
Using the dialog box to enter the function simplifies the task. The dialog box takes care of the function's syntax by entering the function's name, the commas separators, and brackets in the correct locations and quantity.
Point at Cell References
No matter which option you choose to enter the function into a worksheet cell, it is best to use point and click to enter the cell references used as arguments. This minimizes the chance of errors caused by entering the wrong cell reference.
Enter LEFT With the Dialog Box
Follow along with this tutorial to enter the LEFT function and its arguments using the Excel Function Arguments dialog box.
Open a blank worksheet and enter the data for the tutorial.
Select cell B3 to make it the active cell. This is where the results of the function will display.
Select Text to open the function drop-down list.
Choose LEFT to open the Function Arguments dialog box. In Excel for Mac, the Function Builder opens.
Place the cursor in the Text text box.
Select cell A3 in the worksheet to enter that cell reference into the dialog box.
Place the cursor in the Num_chars text box.
Select cell B10 in the worksheet to enter that cell reference.
Select OK. Except for Excel for Mac, where you select Done.
The extracted substring Widget appears in cell B3.
Extract Numbers With the LEFT Function
The LEFT function also extracts a subset of numeric data from a longer number using the steps listed in the previous section. The extracted data is converted to text and cannot be used in calculations involving certain functions, such as the SUM and AVERAGE functions.
One way around this problem is to use the VALUE function to convert the text into a number as shown in row 9 of the example image:
A second option is to use paste special to convert the text to numbers.