What it does
LEFT is the function to reach for when codes are built from fixed-length parts. You give it a text and the number of characters you want; Excel counts from the left and returns that many. Spaces, hyphens and dots count as characters too.
When the length of the part changes from row to row, calculate it instead of typing a number: to take the first name from a full name, find the space with FIND and subtract one. The result is always text; if you need the leading digits as a number, convert them with VALUE.
To take characters from the end, use RIGHT; from the middle, MID. Microsoft 365 also has TEXTBEFORE, which returns everything before a delimiter in one step.
Syntax
=LEFT(text, [num_chars])=SOLDAN(metin; [karakter_sayısı])Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
text | Required | The text to take characters from, or the cell that holds it. Numbers and dates are first turned into text from their raw value; for a date, that is its serial number. |
num_chars | Optional | How many characters to take. If omitted, 1 is used; if it is larger than the text, the whole text comes back. It cannot be negative. |
Examples
1Split the product group from a code
The first three letters of each code show the product group. The formula in C2 is filled down and takes the first three characters in every row.
=LEFT(A2,3)| A | B | C | |
|---|---|---|---|
| 1 | Product Code | Product | Group |
| 2 | KBL-1525 | Copper cable | KBL |
| 3 | PRZ-0210 | Grounded socket | PRZ |
| 4 | KBL-1540 | Armoured cable | KBL |
| 5 | SGT-0016 | Circuit breaker | SGT |
| 6 | ANH-0300 | Light switch | ANH |
- C2 (filled down to C6)
=LEFT(A2,3)
C2 shows KBL and C5 shows SGT. You can now use the Group column as a condition in SUMIF or in a PivotTable.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Take the first name from a full name
The first name has a different length in each row, so FIND works out the number of characters: the position of the space minus one is the length of the first name.
=LEFT(A2,FIND(" ",A2)-1)| A | B | |
|---|---|---|
| 1 | Full Name | First Name |
| 2 | Emma Brown | Emma |
| 3 | Mary Ann Clark | Mary |
| 4 | Lucas Gray | Lucas |
| 5 | Ava Thompson | Ava |
- B2 (filled down to B5)
=LEFT(A2,FIND(" ",A2)-1)
B2 shows Emma. For a person with two first names the formula stops at the first space, so B3 holds only Mary.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
3Take the first digits of a ZIP code as a number
The first three digits of a US ZIP code point to a regional mail processing area. The ZIP codes are stored as text, so the leading zero survives; column C takes the three digits as text, column D turns them into a number with two minus signs.
=LEFT(B2,3)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Branch | ZIP Code | Area (text) | Area (number) |
| 2 | Cambridge | 02139 | 021 | 21 |
| 3 | Newark | 07102 | 071 | 71 |
| 4 | Denver | 80202 | 802 | 802 |
| 5 | Austin | 78701 | 787 | 787 |
- C2 (filled down to C5)
=LEFT(B2,3) - D2 (filled down to D5)
=--LEFT(B2,3)
For Cambridge, C2 shows 021 and D2 shows 21. If you match against a list that stores area numbers as numbers, use column D: the text "021" does not match the number 21.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #VALUE!
Cause: num_chars is negative. This usually comes from a calculation such as
FIND(" ",A2)-1when the character being searched for is missing in that row.Fix: Make sure every row contains the delimiter, or wrap the formula in IFERROR.
Good to know
- If the text starts with an invisible space, that space is the first character. Clean it first:
=LEFT(TRIM(A2),3). - Before splitting codes by position, check that their lengths really are fixed; a helper column with
=LEN(A2)shows the odd ones at a glance.
In Nexell
Get Nexell to do it from a sentence
Put the first three letters of each product code into a new Group column
Convert to tableBring your list into the tool and type this. Nexell adds the Group column with a real Excel text formula and shows it in the preview first; in the downloaded file, the group changes when you change a code.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How do I get everything before the first space?
Find the space with FIND and subtract one: =LEFT(A2,FIND(" ",A2)-1). If a cell has no space, the formula returns #VALUE!; to return the whole text in that case, use =IFERROR(LEFT(A2,FIND(" ",A2)-1),A2).
How do I turn the result of LEFT into a number?
Put two minus signs in front of it or wrap it in VALUE: =--LEFT(A2,4) or =VALUE(LEFT(A2,4)). The result can then be added up and compared with other numbers.