Excel LEFT Function

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"