Excel Exercises Practice Name

Excel LEFT Function

Excel has many functions to isolate certain characters from text, such as LEFT, RIGHT, and MID. The LEFT function returns a shorter string (or "substring") of text from the leftmost characters of a larger string of text. In Excel, a "string" of text is just any text that is enclosed in quotes or is in a cell. The LEFT function returns a string that is composed of a specified number of the first, or leftmost characters from the larger string. Read on to see examples of how the LEFT function works.


Excel LEFT Function Syntax

The LEFT function in Excel returns text of a certain length of characters from the first or leftmost characters of another string. The LEFT function takes two arguments:

=LEFT(text, [length])
  • text - The original string of text, either as a cell reference (such as B1 or C6) or as a string enclosed in quotes (such as "Excel").
  • length - The number of characters to extract from the left end of the original string. If omitted, LEFT will return only one character (the first character of the string).

Examples

=LEFT("Some text", 6)

The function above returns the string "Some t". See explanation below:

=LEFT("Some text")
-------123456789

We take the six characters from the left end of the string (including the space) and we end up with the substring "Some t".

Suppose cell A1 contained the text "Excel Exercises". The function =LEFT(A1, 7) would return "Excel E" - see below.

"Excel Exercises"
-123456789...


More Excel LEFT Function Examples

The LEFT function in Excel works with a text argument in quotes, or with a cell reference. Below are some examples of the LEFT function with a cell reference argument.

A B C D
1 NAME ID AGE SALES
2 Bob 1234 32 $5,000.00
3 Jennifer 5678 45 $7,250.00
=LEFT(A3, 3)

This LEFT formula would return "Jen".

Jennifer
12345678


=LEFT(D1, 2)

This LEFT function would return "SA".

SALES
12345

Using the LEFT Function to Return Digits of a Number

The LEFT function in Excel can also be used to retrieve the leftmost digits of a number. When retrieving the leftmost digits of a number, LEFT will skip over any number formatting punctuation, such as dollar signs, decimals, and thousands-separating commas. For example, in the number 4,300 the comma is a number formatting character, and the actual number is 4300. Therefore, if cell A1 contained 4,300 then =LEFT(A1, 2) would return "43". See the following example from the table above.

=LEFT(D3, 3)

This LEFT formula would return "725".

$7,250.00
-1 234 56

LEFT only ignores number formatting punctuation if the number is, in fact, formatted as a number. Oftentimes in Excel a cell may contain what appears to be a number, but is actually a string of text. If this is the case, LEFT will treat each character as it would treat characters of text.

=LEFT("$7,250.00", 3)

This LEFT formula would return "$7.".

$7.250.00
123456789


Using LEFT With Other Excel Functions

As with any Excel function which takes numerical inputs as arguments, LEFT can accept other functions which return numbers as its inputs.

Take the example below where we use the LEN function as an input to the LEFT function:

=LEFT("Excel Exercises", LEN("cell"))

cell
1234

The LEN function returns 4, so the LEFT function equates to =LEFT("Excel Exercises", 4), which would return "Exce".

"Excel Exercises"
-123456789...


More LEFT Function Examples

The table in the last example is nicely formatted into separate columns for the employee's name, ID, age, and sales. But suppose your boss sends you an Excel file with a single string for each employee, containing their ID, name, and age, all in the same cell!

A
1 1234 Bob 32
2 5678 Jennifer 45

How do you write a reusable function to extract each employee's ID number from each cell?

You know that ID is always 4 characters long, so you can use the following formula:

=LEFT(A1, 4)

This formula would return "1234"

=LEFT(A2, 4)

This formula would return "5678"



Excel Exercises with the LEFT Function

In closing, the LEFT function is used to extract a shorter string of text (a "substring") from the left end of a longer string of text. As with all shortcuts and formulas in Excel, the best way to improve is to try as many hands-on practice exercises as possible. The more repetitions you get, the more quickly and naturally you'll be able to use them in your everyday work. Try a few reps in the quiz below to get familiar with the LEFT function now! Or, if you're ready, try some hands-on Excel Exercises with LEFT.