How To Extract The Text With Excel’s LEFT/LEFTB Function

When the text is copied or imported into Excel, then unwanted garbage characters are sometimes concluded with the good data. Sometimes only some part of the text data in the cell is required.

Excel LEFT and LEFTB Function Syntax

The LEFT and the LEFTB functions perform similar operations, but they are different in the languages supported by them. Then follow the instructions and select the function which is best supported by your language.

  • LEFT – it is for the languages which use the single-byte character set. This group consists of English and all European languages as well.
  • LEFTB – it is for the languages which use the double-byte character set. This consists of Japanese, Chinese (simplified as well as traditional), and Korean.

In Excel, a function Syntax refers to the layout of the function, and it also includes the function’s name, brackets, and arguments.

The syntax for the LEFT function is:

  • =LEFT (Text, Num_chars)

The syntax for the LEFTB function is:

  • =LEFTB (Text, Num_bytes)
  1. Text – it is required for both LEFT as well as LEFTB function. It refers to the entry which contains the desired data.
  2. Num_chars – it is optional for the LEFT function. It specifies the number of characters on the left of the string argument to be retained. All the other characters will be removed.
  3. Num_bytes – it is optional for the LEFTB function. It specifies the number of characters on the left of the string arguments retained in the bytes. It is removing all other characters.

Steps To Enter LEFT With the Dialog Box

  1. Firstly, open a blank worksheet and then enter the data for the tutorial.
  2. Then select the cell B3 to make it an active cell. This is the cell where the results of the function going to are displayed.
  3. After that, select the Formulas.
  4. Select the Text button to open the function drop-down menu.
  5. Then choose the LEFT function to open the Function Arguments dialog box. The Function Builder opens in Excel for Mac.
  6. After this, place the cursor on the text in the Text Box.
  7. Then select the cell A3 in the worksheet to enter crdownload file that cell reference into the dialog box.
  8.  Now place the cursor on the Num_chars text box.
  9. Select cell B10 in the worksheet to enter the cell reference.
  10. Lastly, select the OK button. Except for the Excel for Mac, where you have selected the Done tab.Top of Form And the extracted substring Widget will appear in the cell B3.

Extract Numbers With The LEFT Function

The LEFT function also extracts a subset of the numeric data from the longer number using the steps listed in the previous section. The extracted data will be converted to the text and cannot be used in the calculations involving specific functions, like SUM and AVERAGE functions.

Method 1

By Using the VALUE function to convert the text into a number shown in row 9 of the example image:

  •  =VALUE (LEFT (A8,6))Bottom of Form

Method 2

By Using the Paste special to convert the text to numbers.

Leave a Reply

Your email address will not be published. Required fields are marked *

DMCA.com Protection Status