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.
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("Some text", 6)
The function above returns the string
"Some t". See explanation below:
We take the six characters from the left end of the string (including the space) and we end up with the substring
Suppose cell A1 contained the text
"Excel Exercises". The function
=LEFT(A1, 7) would return
"Excel E" - see below.
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.
This LEFT formula would return "Jen".
This LEFT function would return "SA".
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.
This LEFT formula would return "725".
-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.
This LEFT formula would return "$7.".
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"))
The LEN function returns 4, so the LEFT function equates to
=LEFT("Excel Exercises", 4), which would return
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!
|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:
This formula would return "1234"
This formula would return "5678"