Function guides ยท Text
LEFT function
Returns a number of characters from the start of text.
Syntax
=LEFT(text, [num_chars])
| Argument | What it is |
|---|---|
text | The text or cell to take characters from. |
num_chars | Optional. How many characters. Defaults to 1. |
What LEFT does
LEFT returns a set number of characters from the beginning of a piece of text. Its mirror image, RIGHT, counts from the end, and MID starts wherever you tell it to.
Used with a fixed number, it pulls prefixes: area codes, country codes, department letters. Used with FIND or SEARCH to locate a separator, it pulls the part before a space, a dash or an at sign, however long that part happens to be.
The result is always text, which matters the moment you try to do arithmetic with it.
LEFT examples
1. A fixed-length prefix
| A | B | |
|---|---|---|
| 1 | Part code | Category |
| 2 | ELC-4417 | ELC |
| 3 | HRD-0932 |
Part codes start with a three-letter category. LEFT takes the first three characters of the code. The result is text, even when the characters look like digits.
2. First name from a full name
| A | B | |
|---|---|---|
| 1 | Full name | First name |
| 2 | Mei Tanaka | |
| 3 | Christopher Lee | Christopher |
The first name is a different length every time, so a fixed number will not work. FIND locates the space and LEFT takes everything before it, which is the position of the space minus one.
3. Turn the prefix into a number
| A | B | |
|---|---|---|
| 1 | Invoice | Year |
| 2 | 2026-0412 | 2027 |
LEFT always returns text, so an invoice year pulled from "2026-0412" is the text "2026". Multiply by 1 or wrap the formula in VALUE to turn it into a real number that can be compared or summed.
Common LEFT mistakes
- Forgetting that the result is text.
=LEFT(A2,4)on 2026-0412 returns "2026", which will not sum and will not match the number 2026 in a lookup. Wrap it in VALUE or add 0. - Off-by-one with FIND.
LEFT(A2,FIND(" ",A2))includes the space. Subtract 1 to stop before it. - FIND failing on a missing separator. If a name has no space, FIND returns #VALUE!. Wrap it in IFERROR and return the whole cell:
IFERROR(LEFT(A2,FIND(" ",A2)-1),A2). - Asking for more characters than exist. LEFT quietly returns the whole text. That is harmless, but it hides a bad assumption about the data.
- Using LEFT when Text to Columns would do. For a one-off split of a long list, Data > Text to Columns is faster and needs no formula. Use LEFT when the source updates.
LEFT on Excel job tests
Text-splitting tasks sound like this: "extract the area code from the phone number", "get the department from the employee ID", "split the full name into two columns". The data follows one pattern, with a few awkward rows.
If the piece has a fixed length, put the number straight into LEFT. If not, use FIND to locate the separator first. Know that FIND is case sensitive and SEARCH is not.
LEFT returns text. If the next step is a lookup against numbers, or arithmetic, wrap it in VALUE. A lookup column full of #N/A is the usual symptom.
Related functions
LEFT questions
- What is the difference between LEFT, RIGHT and MID?
- LEFT takes characters from the start, RIGHT from the end, and MID from any starting position you name.
- How do I get everything before a character?
- Use FIND to locate the character and subtract one:
=LEFT(A2,FIND("-",A2)-1). - Why does LEFT return text when I wanted a number?
- All text functions return text. Wrap the result in VALUE, or add 0, to convert it.
- Can LEFT take the first word from a sentence?
- Yes.
=LEFT(A2,FIND(" ",A2&" ")-1)adds a space at the end so it also works when there is only one word.
Practice LEFT
Try it in a working grid: the free lesson LEFT, RIGHT and MID: pull out part of a cell (module 9 of Everyday Excel) has checked LEFT exercises.
Practice this function