LEFT, LEFTB Excel Formula

Author Zaheer    Category Formulas     Tags , ,

When the data is copied or being imported in the excel worksheet unnecessary characters or some words could at times are included in the new data. Excel has more than a few functions you can use depending upon where an unwanted character is situated.

  • If you have an unwanted character on both sides of the good data, make use of the MID function to get rid them.
  • If these have an unwanted character appears on the left side of the good data, make use of the Right Function to get rid of it.
  • When the unwanted character is on the right side of the good data, make use of the Left Function to get rid of them.

Named Arguments Left, LeftB Function

Syntax: Left (string, length) length

Use: Required

Data Type: String

The strings to be processed, length and the number of character to return form left of the strings.

Return Value, Left$ and LeftB$ returns to the String, Left and LeftB return variant of sub type strings.

Description:

Return the string containing the left most length character of the strings. Rules at Glance:

  • If the length is zero, zero-length strings (“”) is returned.
  • If the length is greater than length of the string returned

The Language Reference

If the length is less than zero or null, then the function generate run time error five, Invalid techniques call or arguments’ and the runtime error ninety four, ‘invalid use of the null’, respectively.

  •  If the strings contain Null, then Left returns Null.
  •  If the Left and Left$ course strings of the characters; then the LeftB and LeftB$ course binary data.

Programming Tips and Gotchas

• make use of the Len function to settle on the general length of the string.

• If you use LeftB function with the byte data, then the length specify the digit of bytes to return.

Example: 

This example makes use of the LeftB function to return in the first five bytes from the string.

Dim S as String

S= LeftB (hello world, 5) // return ‘Hello’

Example Using the Excel Left Function: 

  • Enter the subsequent data in the cell C1: 687 miles
  • Click in the cell D1 in spreadsheet – it is where the function would be situated
  • Click in the formulas tab of ribbon menu
  • Choose Text from ribbons to open function drop down list
  • Click in Left on the list to bring up function dialog box
  • In dialog box, click in the Text line
  • Click in Cell C1 on the spreadsheet
  • Click in the num_chars line
  • Enter number three in this line since you just like to keep the 3 leftmost characters of the data
  • Then click OK
  • The number 687 must appear in the cell D1

If you click on the cell D1 the whole function =Left (D1, 3) shows on the formula bar just above the work sheet.

  • LEFT return on the first character in the text string, based in the number of character you specify.
  • LEFTB return the first character in the text string based in the number of bytes specified.

Note: LEFT is planned for use with the languages which uses single-byte character sets (SBCS), while LEFTB is planned for use with the language which uses double-bytes character set or the DBCS. The default language settings on the computers affect the return value on the following way:

  • LEFT constantly counts every character, whether the single-byte or the double-byte, as one, so regardless of what default language setting it has.
  • LEFTB counts every double-bytes character as two when you’ve enabled editing of language which supports DBCS or the double-bytes character set and just set it as default language. Or else, LEFTB counts every character as one.

How to Copy an Example?

  1. Make a blank work book or work sheet.
  2. Opt for example on the Help topic.

Choosing an Example from Help:

  1. Press CTRL plus C.
  2. In the work sheet, opt for cell A1, then press CTRL plus V.
  3. To control between viewing results and viewing formulas that return the result, press CTRL plus (a grave accent), or in the Formulas tab, on the Formula Auditing group, and then click Show Formula button.

Example 2: LEFTB (With the computer set to default language that support DCS)

In the subsequent example:

  • LEFTB return the first two characters, since every character is counted as two
  • LEFT return the first four characters, since every character is counted as one. LEFT return the first four characters regardless of what default language setting is in the computer.

Post comment