All versionsExamples need Excel 2021 or laterText

TEXT

TEKST in Dutch Excel

Turns a number or date into text, formatted the way a format code describes, so it can appear inside labels, keys and sentences.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

ABCD
1RelationCustomerBalanceBalance as text
2D1001Dekker Installatietechniek BV12480.512480,512,480.50=TEXT(C2, "#,##0.00")=TEXT(C2; "#,##0.00")
3D1002Bakkerij Jansen978.4978,4
4D1003Van Dijk Groenvoorziening3150.253150,25
5D1004Studio Mulder64.964,9

Result: 12,480.50

=TEXT(C2, "#,##0.00")
=TEXT(C2; "#,##0.00")

What TEXT does and when to use it

What it does: it turns a number or date into text in the format you choose, for example 12480.5 into '12,480.50' or a date into '2026-09'.

When to use it: whenever you join a number or date to other text with &, which otherwise shows the raw value, such as the serial number 46295 for a date. If the number only has to look different in its own cell, give that cell a custom number format instead: the value stays a number you can add up.

How Excel evaluates it

Excel reads value as a number, and then:

  • it reads format_text and interprets each code character (0, #, separators, date letters) in the language of your Excel
  • it rounds the number to the decimals the code asks for
  • it builds the text character by character, using your regional settings for the decimal and thousands signs and your Excel language for day and month names

The result is a text value, aligned left, even when it looks exactly like a number.

The format codes on this page are written for the page language; they follow the Excel language and the decimal setting, not the formula format chosen in the header.

Watch out: TEXT returns text, not a number: SUM ignores the result and it no longer sorts as a number.

Mental model

A label printer: you feed it a number and a template, it prints a sticker. The sticker looks like the number, but you cannot do sums with a sticker.

Terms used on this page: serial number, cell

Syntax

Official TEXT documentation
TEXT(value, format_text)
TEXT(value; format_text)
ArgumentTypeMeaning
valueRequiredThe number or date to turn into formatted text.
format_textRequiredA format code in quotes; Dutch Excel uses its own letters (jjjj, uu).

Return value:Text: the number formatted with the format code

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, 17 KB)

Beginner

Show a receivable balance with separators and decimals

You are preparing balance confirmation letters (saldobevestigingen) from the client's receivables aging list. The balance of customer D1001 is stored as 12480.5. For the letter you need that amount as text with a thousands separator and two decimals, exactly as the customer expects to read it.

ABCDE
1RelationCustomerBalanceBalance as text
2D1001Dekker Installatietechniek BV12480.512480,512,480.50=TEXT(C2, "#,##0.00")=TEXT(C2; "#,##0.00")
3D1002Bakkerij Jansen978.4978,4
4D1003Van Dijk Groenvoorziening3150.253150,25
5D1004Studio Mulder64.964,9
6
Formula cell (D2)Input

Formula in D2

=TEXT(C2, "#,##0.00")
=TEXT(C2; "#,##0.00")

Result: 12,480.50

Step by step
  1. Excel reads C2: the number 12480.5.

  2. It reads the format code #,##0.00:

    • the comma between # and ## asks for a thousands separator
    • the 0 before the point guarantees at least one digit
    • .00 asks for exactly two decimals
  3. 12480.5 has one decimal, so Excel pads it to two: 12480.50. It inserts the separator after the thousands: 12,480.50.
  4. The result is the text 12,480.50, aligned left in D2. It is 9 characters long, not a number.
  5. Copy the formula to D3 and C3 (978.4) becomes 978.40: no separator is needed below 1,0001.000, and the 0 places still force two decimals.

Watch out: SUM(D2:D5) returns 0, because every cell in column D holds text. Reconcile with column C, not with the formatted copy.

More on this example: why it works

Why it works

Each character in the code has one job: # and 0 hold digit places, the separators say where to group and where the decimals start, and the number of 0 places after the decimal sign fixes the decimals. A Dutch regional setting reads the same pattern with the signs swapped, which is why the code itself can differ between the two settings.

Intermediate

Build fixed-width invoice keys with leading zeros

You match invoices between the sales subledger and the general ledger. The subledger stores invoice numbers as plain numbers: 38, 815, 4217. The general ledger uses text keys like VRK-000038, always six digits after the prefix.

The formula in D3 builds the key for the invoice in row 3. One invoice, imported from an older system, has a seven-digit number.

ABCDE
1Invoice no.RelationAmountLedger key
242174217D1008€2,450.00€ 2.450,00
33838D1001€12,480.50€ 12.480,50VRK-000038="VRK-" & TEXT(A3, "000000")="VRK-" & TEXT(A3; "000000")
4815815D1015€978.40€ 978,40
512345671234567D1022€3,150.25€ 3.150,25
6
Formula cell (D3)Input

Formula in D3

="VRK-" & TEXT(A3, "000000")
="VRK-" & TEXT(A3; "000000")

Result: VRK-000038

Step by step
  1. Excel evaluates TEXT(A3, "000000") first. A3 holds the number 38.
  2. Every 0 in the code is a digit place that is always shown. There are 6 places and 38 fills 2 of them, so the other 4 become zeros: 000038.
  3. The & operator joins the prefix to the padded text: VRK- and 000038 give VRK-000038.
  4. Without TEXT, "VRK-" & A3 gives VRK-38: & converts the number to text but has no way to add the zeros, so the key never matches the ledger.
  5. The same formula applied to A5, the 7-digit number 1234567, would give VRK-1234567. The zeros set a minimum width, not a maximum, so TEXT never cuts digits off.

Watch out: VRK-1234567 is 11 characters, not 10. It will not match a six-digit key in the general ledger: check the length of such keys with LEN and follow up the source number with the client; TEXT will not warn you.

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

Why it works

0 in a format code means 'always show a digit here', so a code of six zeros forces six characters for any number up to 999999.

Joining the result to the prefix produces a text key in the exact shape the general ledger uses, so a lookup can match both sides.

More to watch out for

  • If you need the padded number in its own cell only, a custom number format 000000 on column A shows 000038 and keeps the value 38 as a number.

Variations

=TEXT(A3, "000000")
=TEXT(A3; "000000")

The padded number without a prefix, 000038, for a ledger export that stores the six digits only.

Advanced

Total one month of postings using a year-month text key

For the cut-off test around year end 31 December 2025 you have the journal lines of account 4500 Huisvestingskosten. A test cell must total the debit of the month typed as a key in F1, in the form 2025-12. The key in F1 is stored as text.

Postings on 30 November and 1 January sit one day either side of the month.

ABCDEFG
1DateEntry no.DebitMonth key2025-12
228 Nov 202525-1180€1,250.00€ 1.250,00Total€13,150.00€ 13.150,00=SUMPRODUCT((TEXT(A2:A8, "yyyy-mm") = F1) * C2:C8)=SUMPRODUCT((TEXT(A2:A8; "yyyy-mm") = F1) * C2:C8)
330 Nov 202525-1204€830.00€ 830,00
41 Dec 202525-1231€4,100.00€ 4.100,00
515 Dec 202525-1290€2,300.00€ 2.300,00
631 Dec 202525-1342€6,750.00€ 6.750,00
71 Jan 202626-0001€1,900.00€ 1.900,00
812 Jan 202626-0047€3,400.00€ 3.400,00
9
Formula cell (F2)InputNote: F1 holds the text 2025-12, typed with a leading apostrophe so Excel does not turn it into a date.

Formula in F2

=SUMPRODUCT((TEXT(A2:A8, "yyyy-mm") = F1) * C2:C8)
=SUMPRODUCT((TEXT(A2:A8; "yyyy-mm") = F1) * C2:C8)

Result: €13,150.00€ 13.150,00

Step by step
  1. Excel evaluates TEXT over the whole range A2:A8 with the code yyyy-mm and gets seven keys: {"2025-11"; "2025-11"; "2025-12"; "2025-12"; "2025-12"; "2026-01"; "2026-01"}.
  2. The codes differ between languages (yyyy-mm versus jjjj-mm), but the keys they produce are identical, so the same F1 works in both.
  3. Each key is compared with F1, 2025-12: {FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE}. 30 November and 1 January, one day either side of the month, are both FALSE.
  4. Multiplying by C2:C8 keeps only the December debits: {0; 0; 4,1004.100; 2,3002.300; 6,7506.750; 0; 0}.
  5. SUMPRODUCT adds them: 4,1004.100 + 2,3002.300 + 6,7506.750 = 13,15013.150. Change F1 to 2026-01 and the total becomes 1,9001.900 + 3,4003.400 = 5,3005.300.

Watch out: Type 2025-12 into F1 without the apostrophe and Excel stores it as the date 1 December 2025. A number never equals a text key, so the total silently becomes 0.

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

Why it works

TEXT turns every date into the same kind of key as F1, so a plain equality test can pick the month, whatever day of the month each posting fell on.

SUMPRODUCT is needed because SUMIFS cannot apply TEXT to its criteria range; it only compares the cells as they are.

More to watch out for

  • Excel does not translate format codes inside a formula when a workbook moves between languages. A formula written as jjjj-mm in Dutch Excel no longer produces 2025-12 in English Excel, and the total drops to 0 or turns into an error.

Variations

=SUMIFS(C2:C8, A2:A8, ">="&DATE(2025, 12, 1), A2:A8, "<="&EOMONTH(DATE(2025, 12, 1), 0))
=SUMIFS(C2:C8; A2:A8; ">="&DATE(2025; 12; 1); A2:A8; "<="&EOMONTH(DATE(2025; 12; 1); 0))

Date bounds instead of a text key: no format codes, so it works the same in every Excel language, and SUMIFS is faster on large journal files.

=TEXT(A4, "mmmm yyyy")
=TEXT(A4; "mmmm yyyy")

A readable label for the workpaper heading: December 2025. The month name follows the Excel language, so Dutch Excel shows december 2025.

MasterExcel 2021 or later

Signed difference label with three-section format codes

You reconcile each general ledger account to the client's specification and want a label such as -7,4007.400 (-6.2%) in the workpaper: the ledger balance minus the specification, with an explicit plus or minus sign and the percentage in brackets. No difference must read 0 (0.0%), and an account with a zero specification must not show #DIV/0!.

ABCDE
1AccountSpecificationLedgerDifference
21100 Bank€48,000.00€ 48.000,00€51,250.00€ 51.250,00
31300 Debiteuren€120,000.00€ 120.000,00€112,600.00€ 112.600,00-7,400 (-6.2%)=LET( spec, B3, ledger, C3, diff, ledger - spec, pct, IF(spec = 0, 0, diff / spec), TEXT(diff, "+#,##0;-#,##0;0") & TEXT(pct, " (+0.0%); (-0.0%); (0.0%)") )=LET( spec; B3; ledger; C3; diff; ledger - spec; pct; IF(spec = 0; 0; diff / spec); TEXT(diff; "+#,##0;-#,##0;0") & TEXT(pct; " (+0.0%); (-0.0%); (0.0%)") )
41600 Crediteuren€86,500.00€ 86.500,00€86,500.00€ 86.500,00
51800 Te betalen btw€35,000.00€ 35.000,00€36,120.00€ 36.120,00
6
Formula cell (D3)Input

Formula in D3

=LET(
  spec, B3,
  ledger, C3,
  diff, ledger - spec,
  pct, IF(spec = 0, 0, diff / spec),
  TEXT(diff, "+#,##0;-#,##0;0") & TEXT(pct, " (+0.0%); (-0.0%); (0.0%)")
)
=LET(
  spec; B3;
  ledger; C3;
  diff; ledger - spec;
  pct; IF(spec = 0; 0; diff / spec);
  TEXT(diff; "+#,##0;-#,##0;0") & TEXT(pct; " (+0.0%); (-0.0%); (0.0%)")
)

Result: -7,400 (-6.2%)

Step by step
  1. LET names the inputs: spec is B3, 120,000120.000, and ledger is C3, 112,600112.600. diff = 112,600112.600 - 120,000120.000 = -7,4007.400.
  2. IF checks spec = 0, which is FALSE, so pct = -7,4007.400 / 120,000120.000 = -0.061666...

  3. The first TEXT uses a code with three sections separated by semicolons: positive, negative, zero.

    diff is negative, so Excel uses the second section, -#,##0. A code section for negatives shows the value without its sign, so the - in the section supplies it: -7,400.

  4. The second TEXT also picks its negative section, (-0.0%). The % multiplies by 100 and one decimal is kept: -6.1666... rounds to 6.2, giving (-6.2%) with a leading space.
  5. & joins the two texts: -7,4007.400 (-6.2%). Copied to the 1600 Crediteuren row, diff is 0, the third sections apply and the label reads 0 (0.0%); 1100 Bank gets +3,2503.250 (+6.8%).

Watch out: The mirror-image mistake: a code such as +#,##0;#,##0 has a negative section without a minus, so -7,4007.400 is shown as 7,4007.400. The label looks tidy and reports a ledger that is too low as one that is too high, or the other way round.

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

Why it works

A format code can hold up to three sections, and Excel picks one by the sign of the value, so one code handles surplus, shortfall and zero without an IF. Literal characters such as +, -, brackets and spaces are printed as they are. LET computes diff once and names the rule, and the IF on the specification handles the division edge case before TEXT sees it.

Do not use this when the difference must stay a number for totals, charts or conditional formatting: put the same code on the cell as a custom number format instead. That also survives a switch between Dutch and English Excel, because Excel translates cell formats but not the codes inside a TEXT formula.

More to watch out for

  • Rounding happens only in the text. A difference of -0.04% shows as (-0.0%) in the second section, not 0 (0.0%), because the value is not zero; round pct with ROUND first if tiny differences should read as zero.

Variations

=TEXT(C3 - B3, "+#,##0;-#,##0;0")
=TEXT(C3 - B3; "+#,##0;-#,##0;0")

Only the signed amount, -7,4007.400, when the workpaper has a separate percentage column.

Common mistakes

SymptomCauseFix
Totals over the formatted column are 0Formatting a column of amounts with TEXT and then using that column in totals, which come out as 0.Keep the numbers as numbers and format the cells instead; use TEXT only where the number is joined to other text.
yyyy appears instead of the yearCopying a formula with "dd-mm-yyyy" from an English website into Dutch Excel, and getting yyyy in the result instead of the year.Use the codes of your Excel language: dd-mm-jjjj in Dutch Excel, and uu:mm instead of hh:mm for times.
A date shows as a number such as 46295Joining a date to text with & and seeing a number such as 46295.Wrap the date in TEXT with a date code, so the formula shows the date instead of its serial number.
#N/A although the ID is in the other tablePadding IDs with TEXT for a lookup, while the other table stores the same IDs as numbers, so the lookup returns #N/A.Make both sides the same type: text on both sides, or numbers on both sides.