How to Extract Text With Excel's LEFT/LEFTB Function

Use only the part of the text data you need

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 — such as a person's first name but not the last name.

For instances like these, Excel has functions that can be used to remove the unwanted data from the rest. The function you use depends on where the good data is located relative to the unwanted characters in the cell.

  • If the good data is on the right side, use the RIGHT function to extract it.
  • If the good data has unwanted characters on both sides of it, use the MID function to extract it.
  • If the good data is on the left side, use the LEFT function to extract it.

Excel's LEFT and LEFTB Functions

Excel spreadsheet
Extract Good Text From Bad with the LEFT/LEFTB function. © Ted French

The LEFT and LEFTB functions differ only in the languages they support.

LEFT is for languages that use the single-byte character set. This group includes most languages such as English and all European languages. It is in use in the U.S.

LEFTB is for languages that use the double-byte character set. This includes Japanese, Chinese (Simplified), Chinese (Traditional), and Korean.

The LEFT and LEFTB Functions Syntax and Arguments

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: 

= LEFT ( Text , Num_chars )

The function's arguments tell Excel which data it is to use in the function and the length of the string to be extracted.

The syntax for the LEFTB function is:

= LEFT ( Text , Num_bytes )

The function's arguments tell Excel which data it is 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 can be a cell reference to the location of the data in the worksheet, or it can be 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.

Notes

  • If Num_chars/Num_bytes is omitted, the default value of 1 character is displayed by the function, as in row 4 in the image that accompanies this article.
  • If Num_chars/Num_bytes is greater than the length of the text, the function returns the entire text string, as in row 5 in the example.
  • If the value of the Num_chars/Num_bytes argument is negative, the function returns the #VALUE! error value, as in row 6 of the image.
  • If the value of the Num_chars/Num_bytes argument references a blank cell or is equal to zero, the function returns a blank cell, as in row 7 of the image where Num_chars references the empty cell B13.

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, as in row 2 of the image, and entering cell references for both arguments, as in row 3.

It is usually best to enter cell references for arguments rather than the actual data, so the example lists the steps used to enter the LEFT function and its arguments into cell C3 to extract the word Widgets from the text string in cell A3.

The LEFT Function Dialog Box

Options for entering the function and its arguments into cell B1 include:

  • Typing the complete function: = LEFT (A3,B9) into cell C3
  • Selecting the function and arguments using the function's dialog box

Using the dialog box to enter the function often simplifies the task as the dialog box takes care of the function's syntax — entering the function's name, the commas separators, and brackets in the correct locations and quantity.

Pointing at Cell References

No matter which option you choose for entering the function into a worksheet cell, it is best to use point and click to enter the cell references used as arguments to minimize the chance of errors caused by typing in the wrong cell reference.

Using the LEFT Function Dialog Box

Enter the LEFT function and its arguments into cell C3 of the example image using the function's dialog box.

  1. Click on cell C3 to make it the active cell. This is where the results of the function will be displayed.
  2. Click on the Formulas tab of the ribbon menu.
  3. Choose Text from the ribbon to open the function drop-down list.
  4. Click on LEFT in the list to bring up the function's dialog box.
  5. In the dialog box, click on the Text line.
  6. Click on cell A3 in the worksheet to enter that cell reference into the dialog box.
  7. Click on the Num_chars line.
  8. Click on cell B9 in the worksheet to enter that cell reference.
  9. Click OK to complete the function and return to the worksheet.
  10. The extracted substring Widgets should appear in cell C3.

When you click on cell C3, the complete function =LEFT(A3,B9) appears in the formula bar above the worksheet.

Extracting Numbers With the LEFT Function

The LEFT function can be used to extract a subset of numeric data from a longer number using the steps listed in the previous section.

The only problem is that 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:

=VALUE ( LEFT ( B2, 6 ))

A second option is to use paste special to convert the text to numbers.