Skip to content

Excel function · Text

Excel LEFT function

LEFT returns the number of characters you ask for from the start of a text: the first three letters of a product code, the area part of a ZIP code, the first name from a full name.

In Turkish Excel: SOLDAN

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

English Excel=LEFT(text, [num_chars])
Turkish Excel=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

ArgumentRequiredDescription
textRequiredThe 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_charsOptionalHow 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.

C2fx=LEFT(A2,3)
Split the product group from a code: example table
ABC
1Product CodeProductGroup
2KBL-1525Copper cableKBL
3PRZ-0210Grounded socketPRZ
4KBL-1540Armoured cableKBL
5SGT-0016Circuit breakerSGT
6ANH-0300Light switchANH
  • 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.

B2fx=LEFT(A2,FIND(" ",A2)-1)
Take the first name from a full name: example table
AB
1Full NameFirst Name
2Emma BrownEmma
3Mary Ann ClarkMary
4Lucas GrayLucas
5Ava ThompsonAva
  • 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.

C2fx=LEFT(B2,3)
Take the first digits of a ZIP code as a number: example table
ABCD
1BranchZIP CodeArea (text)Area (number)
2Cambridge0213902121
3Newark0710207171
4Denver80202802802
5Austin78701787787
  • 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)-1 when the character being searched for is missing in that row.

Fix: Make sure every row contains the delimiter, or wrap the formula in IFERROR.

Result does not add up

Cause: LEFT always returns text. The "2025" taken with =LEFT(A2,4) is not a number, so SUM skips it.

Fix: Convert the result with --LEFT(A2,4) or VALUE.

Odd result from a date

Cause: Characters taken from a date come from its serial number, not from what the cell shows: for 15 March 2025, =LEFT(A2,2) returns "45".

Fix: Use DAY, MONTH or YEAR; if you need text, format the date with TEXT.

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

Type this in Nexell

Put the first three letters of each product code into a new Group column

Convert to table

Bring 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.

Related functions

All text functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free