All versionsExamples need Excel 2021 or laterText

LEFT

LINKS in Dutch Excel

Returns a chosen number of characters from the start of a text, counting from the first character.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

ABC
1Entry no.AmountJournal
2VRK-25-0412€12,100.00€ 12.100,00VRK=LEFT(A2, 3)=LEFT(A2; 3)
3INK-25-0388€4,235.50€ 4.235,50
4BNK-25-0071€8,450.00€ 8.450,00
5MEM-25-0031€25,000.00€ 25.000,00

Result: VRK

=LEFT(A2, 3)
=LEFT(A2; 3)

What LEFT does and when to use it

What it does: it returns the first characters of a text, for example the journal code at the start of an entry number.

When to use it: when the part you need sits at the start and you know its length, or can find it with FIND. For a part that ends at a delimiter, TEXTBEFORE reads more clearly (Excel 2024 or Microsoft 365). For the end of a text use RIGHT, for the middle MID.

How Excel evaluates it

Excel reads text first. If it is a number, Excel converts it to text in its stored form, so 4120 becomes the four characters 4, 1, 2, 0.

Then it reads num_chars, or uses 1 when you left it out. It counts that many characters from position 1 and returns them as a new text value. Asking for more characters than there are is fine: you get the whole text.

The result is always text, which is why it aligns left in the cell and does not add up in SUM.

Watch out: LEFT always returns text, even when it looks like a number: from the account number 4120, LEFT(A2, 2) gives the text 41. A SUM of such results is 0 and a lookup against the number 41 returns #N/A. Wrap LEFT in VALUE, or put two minus signs in front (--LEFT(A2, 2)), when you need a number.

Mental model

A pair of scissors that always starts cutting at the left edge of the strip; you only decide how far in the cut goes.

Terms used on this page: cell

Syntax

Official LEFT documentation
LEFT(text, [num_chars])
LEFT(text; [num_chars])
ArgumentTypeMeaningDefault
textRequiredThe text to take characters from; usually a cell reference.
num_charsOptionalHow many characters to take from the left. Leave it out and you get 1.1

Return value:Text: num_chars characters from the start of text

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 5 sheets, 16 KB)

Beginner

Read the journal code from an entry number

In the XAF auditfile of Veldhuis Installatie BV every entry number starts with a three-letter journal code and a hyphen:

  • VRK for sales
  • INK for purchases
  • BNK for bank
  • MEM for memorial entries

For journal entry testing you want the code in a column of its own, so you can count and filter per journal.

ABCD
1Entry no.AmountJournal
2VRK-25-0412€12,100.00€ 12.100,00VRK=LEFT(A2, 3)=LEFT(A2; 3)
3INK-25-0388€4,235.50€ 4.235,50
4BNK-25-0071€8,450.00€ 8.450,00
5MEM-25-0031€25,000.00€ 25.000,00
6
Formula cell (C2)Input

Formula in C2

=LEFT(A2, 3)
=LEFT(A2; 3)

Result: VRK

Step by step
  1. Excel reads text: A2 holds VRK-25-0412, 11 characters. Counted from the left: V, R and K are positions 1 to 3, the first hyphen is 4, 2 and 5 are 5 and 6, the second hyphen is 7, and the digits 0, 4, 1, 2 are 8 to 11.
  2. Next it reads num_chars: 3, so it takes positions 1 to 3 and nothing else.
  3. Positions 1 to 3 are V, R and K. LEFT returns the text VRK, and C2 shows VRK.
  4. Copy C2 down to C5: INK-25-0388 gives INK, BNK-25-0071 gives BNK, MEM-25-0031 gives MEM. The same 3 works for every row because every journal code has the same width.
  5. Change the 3 to 4 and C2 shows VRK- with the hyphen: num_chars counts every character, including punctuation.

Watch out: If an entry number was exported with a leading space, LEFT returns a space and the first two letters, for example " VR". The count per journal then misses that row. TRIM the source first if the numbers are not straight from the auditfile.

More on this example: why it works, 1 variation

Why it works

The journal code has a fixed width and sits at the very start of the entry number, which is exactly the shape LEFT is built for. No searching is needed, because position 1 is always where the code begins and position 3 is always where it ends.

Variations

=LEFT(A2:A5, 3)
=LEFT(A2:A5; 3)

In Excel 2021 or Microsoft 365 one formula in C2 spills the four journal codes.

Intermediate

Get the account class as a real number

You received the trial balance (saldibalans) of Veldhuis Installatie BV as of 31 December 2025. The first digit of each general ledger account is its class:

  • 1 for receivables and payables
  • 4 for operating costs
  • 8 for revenue

You want the class as a number in a column, so you can filter on it and compare it with other numbers when you map accounts to financial statement lines.

ABCDE
1AccountDescriptionBalanceClass
241204120Office rent€84,200.00€ 84.200,0044=VALUE(LEFT(A2))=VALUE(LEFT(A2))
380008000Revenue-€1,254,300.00€ -1.254.300,00
440004000Personnel costs€412,650.50€ 412.650,50
513001300Receivables€186,430.00€ 186.430,00
616001600Payables-€97,210.00€ -97.210,00
7
Formula cell (D2)InputNote: The account numbers in column A are real numbers, not text.

Formula in D2

=VALUE(LEFT(A2))
=VALUE(LEFT(A2))

Result: 44

Step by step
  1. Excel evaluates LEFT(A2) first. A2 holds the number 4120, so LEFT converts it to the text 4120: four characters, 4, 1, 2, 0.
  2. num_chars is left out, so Excel uses the default, 1. It takes position 1 and returns the text 4.
  3. Without VALUE, D2 would show 4 aligned to the left. A test such as =D2=4 would return FALSE, because the text 4 and the number 4 are different values to Excel.
  4. VALUE turns the text 4 into the number 4. D2 shows 4, aligned right, and =D2=4 now returns TRUE.
  5. Copy D2 down to D6: 8000 gives 8, 4000 gives 4, 1300 gives 1, 1600 gives 1. A filter on column D for the value 4 now finds the office rent and the personnel costs.

Watch out: If some account numbers were imported as text with a leading space, LEFT returns the space, and VALUE of a space is #VALUE!. The error is a signal here, not a problem: clean the source column with TRIM.

More on this example: why it works, 1 more pitfall, 2 variations

Why it works

LEFT always works on text, so it silently converts the account number and returns text. Leaving out num_chars is correct here because one digit is exactly what you need; it is the one case where the default does the work. VALUE is the step that turns the result back into something that compares and calculates as a number.

More to watch out for

  • A five-digit account such as 12100 still returns 1, which may or may not be the class the client's chart of accounts intends. Check that every account really has the class in its first digit.

Variations

=--LEFT(A2)
=--LEFT(A2)

Two minus signs do the same conversion as VALUE: the first makes the text negative as a number, the second makes it positive again.

=INT(A2 / 1000)
=INT(A2 / 1000)

Pure arithmetic, no text step: works only while every account has exactly four digits.

Advanced

Take the invoice number from a journal description

In the purchase journal of Veldhuis Installatie BV the description starts with the invoice number, then a slash and the supplier name. Invoice numbers differ in length. To match the ledger against the payables list you want only the invoice number. One posting was typed with a space instead of the slash, and that row should say so instead of showing an error.

ABCD
1DescriptionDebitInvoice no.
225-0412/Bakker Techniek€12,100.00€ 12.100,0025-0412=IFERROR(LEFT(A2, FIND("/", A2) - 1), "No / in description")=IFERROR(LEFT(A2; FIND("/"; A2) - 1); "No / in description")
325-10388/Van Dijk Groep€8,450.50€ 8.450,50
425-0455 Visser Logistiek€3,290.00€ 3.290,00
525-0471/Smit Verhuur€560.00€ 560,00
6
Formula cell (C2)Input

Formula in C2

=IFERROR(LEFT(A2, FIND("/", A2) - 1), "No / in description")
=IFERROR(LEFT(A2; FIND("/"; A2) - 1); "No / in description")

Result: 25-0412

Step by step
  1. Excel starts with the innermost function, FIND("/", A2). A2 is 25-0412/Bakker Techniek, 23 characters. Counting from the left, 25-0412 is 1 to 7 and the slash is character 8. FIND returns 8.
  2. Then the subtraction: 8 - 1 = 7. You want everything before the slash, not the slash itself, so the length is one less than its position.
  3. LEFT(A2, 7) takes characters 1 to 7 and returns 25-0412. No error occurred, so IFERROR passes that result through and C2 shows 25-0412.
  4. Copied to C3, FIND returns 9 for 25-10388/Van Dijk Groep, 9 - 1 = 8, and LEFT returns 25-10388. The length adapts per row because FIND measures it each time.
  5. In C4, A4 is 25-0455 Visser Logistiek, with no slash. FIND finds no match and returns #VALUE!. The subtraction and LEFT pass the error on.
  6. IFERROR catches the #VALUE! and returns the text No / in description instead, which is the signal to look up that posting in the auditfile.

Watch out: Forget the - 1 and every result ends with the slash: 25-0412/. It looks almost right, so it slips into a match against the payables list, where it finds nothing.

More on this example: why it works, 2 more pitfalls, 2 variations

Why it works

LEFT needs a length, and FIND supplies one that fits each row: the position of the delimiter minus one. The same shape works for any delimiter, a space for the first word, a hyphen for code prefixes. IFERROR sits outside so that it catches the only failure this formula expects, a missing delimiter.

More to watch out for

  • A description such as Creditnota 25-0412/Bakker Techniek returns Creditnota 25-0412 without any error. LEFT cuts where FIND points; it does not know what an invoice number looks like.
  • IFERROR also hides errors you did not plan for, such as an #N/A that was already in A2. If the source can contain errors, test with ISNUMBER(FIND(...)) instead, as in the variation.

Variations

=IF(ISNUMBER(FIND("/", A2)), LEFT(A2, FIND("/", A2) - 1), "No / in description")
=IF(ISNUMBER(FIND("/"; A2)); LEFT(A2; FIND("/"; A2) - 1); "No / in description")

Tests for the slash explicitly, so an unrelated error in A2 still shows instead of being hidden.

=LEFT(A4, FIND(" ", A4) - 1)
=LEFT(A4; FIND(" "; A4) - 1)

The same construction with a space as delimiter returns the first word, for example 25-0455 from the posting without a slash.

MasterExcel 2021 or later

Split off a letter prefix of any length

In the fixed asset register (activastaat) of Veldhuis Installatie BV every asset code has a category prefix of letters followed by digits, with no delimiter in between: MACH0147, ICT32, VERV0008. The prefix is two to five letters long.

Two codes break the pattern: one has no digits at all (GROND), and one starts with a digit (3D0004). To test depreciation per category you need the prefix, and the formula has to cope with all of these.

ABCD
1Asset codeCostPrefix
2MACH0147€184,500.00€ 184.500,00MACH=LET( code, A2, firstDigit, MIN(FIND({0,1,2,3,4,5,6,7,8,9}, code & "0123456789")), LEFT(code, firstDigit - 1) )=LET( code; A2; firstDigit; MIN(FIND({0\1\2\3\4\5\6\7\8\9}; code & "0123456789")); LEFT(code; firstDigit - 1) )
3ICT32€6,200.00€ 6.200,00
4VERV0008€38,900.00€ 38.900,00
5GROND€250,000.00€ 250.000,00
63D0004€12,750.00€ 12.750,00
7
Formula cell (C2)Input

Formula in C2

=LET(
  code, A2,
  firstDigit, MIN(FIND({0,1,2,3,4,5,6,7,8,9}, code & "0123456789")),
  LEFT(code, firstDigit - 1)
)
=LET(
  code; A2;
  firstDigit; MIN(FIND({0\1\2\3\4\5\6\7\8\9}; code & "0123456789"));
  LEFT(code; firstDigit - 1)
)

Result: MACH

Step by step
  1. code becomes A2, MACH0147. Then Excel builds code & "0123456789": MACH01470123456789, 18 characters. The ten digits added at the end make sure that every digit occurs at least once.
  2. FIND receives an array of ten values to look for, 0 to 9, and returns ten positions, one per digit: 0 at 5, 1 at 6, 2 at 11, 3 at 12, 4 at 7, 5 at 14, 6 at 15, 7 at 8, 8 at 17, 9 at 18. Digits that are not in MACH0147 are found in the added tail, never as an error.
  3. MIN takes the smallest of the ten: 5. That is where the first digit of the code sits, and firstDigit becomes 5.
  4. LEFT(code, 5 - 1) is LEFT(code, 4), which returns MACH. C2 shows MACH.
  5. Copied to C3, ICT32 has its first digit at 4, so LEFT takes 3 characters: ICT. For VERV0008 the smallest position is 5 and the result is VERV.
  6. GROND has no digits. Every digit is found in the tail, the smallest position is 6, and LEFT(code, 5) returns the whole code, GROND. 3D0004 starts with a digit, the smallest position is 1, and LEFT(code, 0) returns empty text, which is correct: there is no prefix.

Watch out: The mirror-image mistake is MAX instead of MIN. With the tail appended, MAX always lands in the tail, so LEFT returns the whole code: MACH0147 comes back unchanged. It looks like a plausible prefix list at a glance and is wrong on every row that has digits.

More on this example: why it works, 2 more pitfalls, 2 variations

Why it works

FIND accepts an array as find_text and then returns an array of positions. Appending all ten digits is a sentinel: it turns every would-be #VALUE! into a harmless large position, so MIN never sees an error and the no-digit case falls out naturally. LET names the one number that matters, the position of the first digit, so the final LEFT reads like the rule.

Do not use this when the prefix has a fixed width (LEFT(A2, 3) is clearer) or when a delimiter separates the parts; then TEXTBEFORE or LEFT with a single FIND is the better choice.

For a one-time split of a column, Flash Fill (Ctrl+E) learns the pattern from two typed examples, at the cost of not updating when the data changes.

More to watch out for

  • Leave out the & "0123456789" and FIND returns #VALUE! for every digit that is not in the code. MIN of an array with an error is an error, so almost every row fails.
  • A code with a lowercase suffix after the digits, such as MACH0147b, still returns MACH, which is right. But a code with digits inside the prefix, such as P2P0012, returns P. The formula defines the prefix as everything before the first digit; check that the client's asset codes follow that rule.

Variations

=LEFT(A2, MIN(FIND({0,1,2,3,4,5,6,7,8,9}, A2 & "0123456789")) - 1)
=LEFT(A2; MIN(FIND({0\1\2\3\4\5\6\7\8\9}; A2 & "0123456789")) - 1)

The same logic without LET, for a helper column in Excel versions before 2021.

=MID(A2, MIN(FIND({0,1,2,3,4,5,6,7,8,9}, A2 & "0123456789")), 100)
=MID(A2; MIN(FIND({0\1\2\3\4\5\6\7\8\9}; A2 & "0123456789")); 100)

The other half: MID from the first digit returns the numeric part, 0147.

Common mistakes

SymptomCauseFix
Only the first letter of the codeYou wrote =LEFT(A2) expecting the whole code and got only the first letter.num_chars defaults to 1, not to the full length. Always type the second argument unless one character is what you want.
46 instead of the yearLEFT on a date returns 46 instead of the year.A date is stored as a serial number (46000-something in 2026), and LEFT reads that number. Use YEAR for the year, or TEXT with a format code if you need text.

Used in combinations

  • Find the customer with a regex lookupPull the invoice number out of a bank description with REGEXEXTRACT, turn it into a pattern, and let XLOOKUP match that pattern against an invoice list that is typed in different ways.Excel for Microsoft 365