LEFT(), LEFTB()

Syntax

• LEFT(text,num_chars)

• LEFTB(text,num_bytes)

Definition. The LEFT() function returns the first characters of a string. The LEFTB() function is used for double-byte characters and returns the first bytes.

Arguments

• text (required). The string containing the characters you want to extract

• num_chars/num_bytes (optional). Specifies how many characters to extract

Background. Use the LEFT() function to extract the first part of a string. You can enter letters or numbers in the text argument. The functions LEFT(), RIGHT(), and MID() are especially useful if strings have a particular pattern, as in the case with ZIP Codes, locations, or ISBNs.

The num_chars argument has to be greater than or equal to 0. If the value of num_chars is greater than the value of the text argument, the LEFT() function returns the entire string. If the num_chars argument is not specified, the default value of 1 is used.

Example. A list of names is entered into a spreadsheet column with the first name separated from the last name with a space. Cell H4 contains a name. The following formula extracts the first name:

=LEFT(H4,SEARCH(" ",H4)-1)

You use the SEARCH() function to determine the position of the space between the first and last names (see Figure 8-5). If you subtract 1 from the result, you get the position of the last character of the first name (the number of characters from the left).

Figure 8-5. Using the LEFT() function together with the SEARCH() function.

Here are some further examples:

• =LEFT("steamboat",5) returns steam.

• =LEFT("gazelle",4) returns gaze.

• =LEFT("Oliver Kiel",5) returns Oliver.

• =LEFT("Excel",1) returns E.

• =LEFT("Excel",2) returns Ex.

See Also

MID(), RIGHT()

Sample Files

Use the LEFT worksheet in the Text_Data.xls or Text_Data.xlsx sample file. The sample files are found in the Chapter08 folder. For more information about the sample files, see the section titled Using the Sample Files.