Excel 2019 or laterExamples need Excel 2021 or laterText

CONCAT

TEKST.SAMENV in Dutch Excel

Joins texts, cells and whole ranges into one text, in the order you list them, with nothing in between.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

ABCD
1JournalEntry no.LineKey
2VRK25-041211VRK-25-0412-1=CONCAT(A2, "-", B2, "-", C2)=CONCAT(A2; "-"; B2; "-"; C2)
3VRK25-041222
4INK25-018811
5BNK25-103133
6MEM25-00071212

Result: VRK-25-0412-1

=CONCAT(A2, "-", B2, "-", C2)
=CONCAT(A2; "-"; B2; "-"; C2)

What CONCAT does and when to use it

What it does: it glues pieces of text together into one text, for example a location, a batch number and a period into one unique key.

When to use it: to build codes and keys, or to put values from the sheet into a sentence. It replaces CONCATENATE. For one separator between all items, or to skip blank cells, use TEXTJOIN. For a few cells, the operator & does the same.

Two main gains:

  • Takes a range: CONCAT(A2:D2) joins four cells, where CONCATENATE needs each cell listed separately.
  • Takes an array: a computed array collapses into one short string.

How Excel evaluates it

Excel reads the arguments from left to right and places them end to end:

  • text is taken as it is
  • a number is converted to text in General format, so 7 formatted as 007 arrives as 7 and 0.5 formatted as 50% arrives as 0.5
  • a date is a number underneath, so it arrives as its serial number (46127 for 15 April 2026)
  • a range is read cell by cell, row by row, and a blank cell contributes nothing

The result is always text. 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: Joined text keeps no formatting from the cells: numbers lose their display format and dates arrive as serial numbers. Wrap them in TEXT with a format code to decide how they are written.

Mental model

A label printer that takes strips of paper in a fixed order and tapes them end to end. It prints numbers in their raw form, and it never adds a gap you did not hand it.

Terms used on this page: worksheet, operator, serial number, array, argument

Syntax

Official CONCAT documentation
CONCAT(text1, [text2], ...)
CONCAT(text1; [text2]; ...)
ArgumentTypeMeaning
text1RequiredThe first text, cell or range to join.
text2OptionalMore texts, cells or ranges; up to 253 arguments in total.

Return value:Text: all the arguments joined into one string

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

Build a unique key for each journal line

You received the XAF auditfile as one row per journal line. To match the lines against the client's subledger export, you need one key per line: journal, entry number and line number, such as VRK-25-0412-1. You add a column that builds the key from the three columns.

ABCDE
1JournalEntry no.LineKey
2VRK25-041211VRK-25-0412-1=CONCAT(A2, "-", B2, "-", C2)=CONCAT(A2; "-"; B2; "-"; C2)
3VRK25-041222
4INK25-018811
5BNK25-103133
6MEM25-00071212
7
Formula cell (D2)Input

Formula in D2

=CONCAT(A2, "-", B2, "-", C2)
=CONCAT(A2; "-"; B2; "-"; C2)

Result: VRK-25-0412-1

Step by step
  1. Excel reads the first argument, A2, and takes the text VRK.
  2. The second argument is the hyphen in quotes. It is placed directly after VRK: VRK-.
  3. The third argument, B2, adds the entry number 25-0412 and the fourth adds another hyphen: VRK-25-0412-.
  4. The fifth argument, C2, holds the number 1. CONCAT converts it to the text 1 and adds it: VRK-25-0412-1, 13 characters.
  5. D2 shows VRK-25-0412-1, aligned left because it is text. Copy D2 down and D3 shows VRK-25-0412-2, D6 shows MEM-25-0007-12.

Watch out: Leave out the hyphens and line 12 of entry 25-0007 becomes MEM25-000712, which is hard to read and can collide with line 2 of entry 25-00071 in the same journal.

More on this example: why it works, 1 variation

Why it works

CONCAT takes its arguments in the order you write them and puts them end to end. The hyphens appear because they are arguments of their own; CONCAT would not add them. The line number needs no special treatment because a whole number looks the same as text as it does as a number.

Variations

=A2 & "-" & B2 & "-" & C2
=A2 & "-" & B2 & "-" & C2

The & operator gives the same result and works in every Excel version, including those without CONCAT.

Intermediate

Entry keys with a four-digit sequence number

The client's subledger export holds one code per entry without separators: journal, year, period and a four-digit sequence number, such as VRK25030412. The XAF file stores the sequence as a plain number (412, 88, 7). You join the first three columns as one range and pad the sequence number, so you can match the entries.

ABCDEF
1JournalYearPeriodSequenceEntry code
2VRK2503412412VRK25030412=CONCAT(A2:C2, TEXT(D2, "0000"))=CONCAT(A2:C2; TEXT(D2; "0000"))
3INK25118888
4BNK25121,0311.031
5MEM251277
6VRK250999
7
Formula cell (E2)InputNote: Year and period are text, as the export delivers them. Column D holds real numbers; the four-digit form exists only in the code.

Formula in E2

=CONCAT(A2:C2, TEXT(D2, "0000"))
=CONCAT(A2:C2; TEXT(D2; "0000"))

Result: VRK25030412

Step by step
  1. Excel reads the first argument, the range A2:C2, cell by cell from left to right: VRK, 25, 03. Together they give VRK2503.
  2. The second argument is TEXT(D2, "0000"). D2 holds 412. The format code 0000 asks for at least four digits and fills the gaps with zeros, so TEXT returns the text 0412.
  3. CONCAT places 0412 after VRK2503. E2 shows VRK25030412, 11 characters.
  4. Without TEXT, as CONCAT(A2:D2), the number 412 arrives in General format and the code is VRK2503412, 10 characters. That is true even if you format D2 to display 0412: CONCAT reads the stored value, not the display.
  5. Copy E2 down: E4 gives BNK25121031, because 1031 already has four digits and 0000 adds nothing; E5 gives MEM25120007.

Watch out: A sequence number of 10000 or higher is written in full by the 0000 code, so the key becomes one character longer. If the client's code is strictly four digits, check the length of the keys.

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

Why it works

A range as one argument is what separates CONCAT from CONCATENATE: A2:C2 counts as one argument but contributes three cells. The padding has to happen before the join, because once the number has been turned into text by CONCAT, the zeros can no longer be added by formatting.

More to watch out for

  • If one of the period cells is blank, the range still joins without complaint and the key loses two characters: VRK250412. The key looks plausible and matches nothing.

Variations

=A2 & B2 & C2 & TEXT(D2, "0000")
=A2 & B2 & C2 & TEXT(D2; "0000")

The same with &, for when the workbook must also work in Excel 2016 or older.

Advanced

An exception line with amount and posting date

For journal entry testing you selected the manual entries posted on a weekend before year end 31 december 2025. For the work paper and the e-mail to the controller, each exception must read like this: 25-0412: 12,040.75 posted on 2025-12-27. You build the lines from the selection; the amount and the date are real numbers in the sheet.

ABCDE
1Entry no.AmountPosting dateException line
225-0412€12,040.75€ 12.040,7527 Dec 202525-0412: 12,040.75 posted on 2025-12-27=CONCAT(A2, ": ", TEXT(B2, "#,##0.00"), " posted on ", TEXT(C2, "yyyy-mm-dd"))=CONCAT(A2; ": "; TEXT(B2; "#,##0.00"); " posted on "; TEXT(C2; "yyyy-mm-dd"))
325-0413€385.50€ 385,5028 Dec 2025
425-0398€50,000.00€ 50.000,0020 Dec 2025
525-0421€7,250.20€ 7.250,2014 Dec 2025
6
Formula cell (D2)Input

Formula in D2

=CONCAT(A2, ": ", TEXT(B2, "#,##0.00"), " posted on ", TEXT(C2, "yyyy-mm-dd"))
=CONCAT(A2; ": "; TEXT(B2; "#,##0.00"); " posted on "; TEXT(C2; "yyyy-mm-dd"))

Result: 25-0412: 12,040.75 posted on 2025-12-27

Step by step
  1. Excel evaluates the two TEXT functions first. TEXT(B2, "#,##0.00") takes 12040.75 and writes it with a thousands separator and two decimals: the text 12,040.75.
  2. TEXT(C2, "yyyy-mm-dd") takes the date in C2, which Excel stores as the serial number 46018 (45658 for 1 January 2025, plus 360 days to reach 27 December), and writes it as 2025-12-27, a Saturday.

  3. CONCAT now joins five pieces in order:

    • 25-0412
    • ": "
    • 12,040.75
    • " posted on "
    • 2025-12-27
  4. D2 shows 25-0412: 12,040.75 posted on 2025-12-27. The spaces around the words posted on come from the placeholder text itself; CONCAT added none.
  5. Remove both TEXT functions and write CONCAT(A2, ": ", B2, " posted on ", C2): the line becomes 25-0412: 12040.75 posted on 46018. The amount loses its separator, and the date turns into its serial number.
  6. Copy D2 to D3: 385.5 becomes 385.50 thanks to the .00 in the code, and the line reads 25-0413: 385.50 posted on 2025-12-28. D4 shows the round amount 50,000.0050.000,00, a second reason to list that entry.

Watch out: Copy this formula from an English workbook into Dutch Excel by hand and the date code yyyy is not recognized as a year. The line still appears, with the wrong text in the date position, and nothing warns you. In a work paper that is an error a reviewer may not catch.

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

Why it works

CONCAT only ever sees values, never formats. TEXT is the bridge: it turns a number or date into text that looks the way you choose, and CONCAT then copies that text as it is.

The format codes are language-dependent, which is why they sit in placeholders: Dutch Excel reads jjjj for the year and uses a period for thousands and a comma for decimals.

More to watch out for

  • The amount in the text is frozen as text. If someone later sums a column of these lines, or sorts them, the 12,040.7512.040,75 line sorts by its characters, not by its value.

Variations

=A2 & ": " & TEXT(B2, "#,##0.00") & " posted on " & TEXT(C2, "yyyy-mm-dd")
=A2 & ": " & TEXT(B2; "#,##0.00") & " posted on " & TEXT(C2; "yyyy-mm-dd")

The & version: identical result, and for a fixed sentence of five pieces many people find it simpler to read.

=TEXTJOIN(" ", TRUE, A2, TEXT(B2, "#,##0.00"), TEXT(C2, "yyyy-mm-dd"))
=TEXTJOIN(" "; TRUE; A2; TEXT(B2; "#,##0.00"); TEXT(C2; "yyyy-mm-dd"))

When every piece gets the same separator, TEXTJOIN adds it for you: 25-0412 12,040.75 2025-12-27.

MasterExcel 2021 or later

A reconciliation status string from monthly differences

During the interim audit you reconcile four ledger accounts to the client's specifications per month. For each account you have the monthly difference (ledger minus specification). The audit file needs one short string per account, one character per month:

  • + when the difference is within the reporting threshold of 500 euro for clearly insignificant differences
  • ! when it is larger
  • a dot (.) for a month whose specification has not been received yet

The September specification is still outstanding for every account.

ABCDEFGHI
1AccountMayJunJulAugSepStatusThreshold
213001300€120.00€ 120,00-€350.00€ -350,00€500.00€ 500,00-€80.00€ -80,00++++.=LET( diffs, B2:F2, limit, $H$2, marks, IF(diffs = "", ".", IF(ABS(diffs) <= limit, "+", "!")), CONCAT(marks) )=LET( diffs; B2:F2; limit; $H$2; marks; IF(diffs = ""; "."; IF(ABS(diffs) <= limit; "+"; "!")); CONCAT(marks) )500500
316001600€0.00€ 0,00€1,250.00€ 1.250,00-€75.00€ -75,00-€640.00€ -640,00
411001100€0.00€ 0,00-€20.00€ -20,00€0.00€ 0,00€2,400.00€ 2.400,00
545004500€900.00€ 900,00-€510.00€ -510,00€430.00€ 430,00€500.00€ 500,00
6
Formula cell (G2)Input

Formula in G2

=LET(
  diffs, B2:F2,
  limit, $H$2,
  marks, IF(diffs = "", ".", IF(ABS(diffs) <= limit, "+", "!")),
  CONCAT(marks)
)
=LET(
  diffs; B2:F2;
  limit; $H$2;
  marks; IF(diffs = ""; "."; IF(ABS(diffs) <= limit; "+"; "!"));
  CONCAT(marks)
)

Result: ++++.

Step by step
  1. LET names the inputs: diffs is the row B2:F2, five cells, and limit is H2, 500. The $ on H2 keeps the threshold fixed when the formula is copied down to G5.
  2. marks tests the five cells one by one and returns a five-item array. The outer IF checks for blanks first: only F2 is empty, so position 5 becomes a dot.

  3. For the other four, the inner IF compares the absolute difference with 500:

    • ABS(120) = 120 <= 500 gives +
    • ABS(-350) = 350 <= 500 gives +
    • ABS(500) = 500 <= 500 gives +, because a difference exactly at the threshold is still within it
    • ABS(-80) = 80 <= 500 gives +

    marks is now {+, +, +, +, .}.

  4. CONCAT(marks) receives the array as one argument and joins its five items with nothing in between. G2 shows ++++., 5 characters.

  5. Copied down, the other accounts give:

    • G3: +!+!. (1,2501.250 and 640 exceed 500)
    • G4: +++!. (only 2,4002.400 exceeds it)
    • G5: !!++. (900 and 510 exceed 500, while 430 and 500 do not)
  6. When the September specification arrives, F2 is no longer blank, the dot in position 5 turns into + or !, and the string stays five characters long.

Watch out: The mirror-image mistake is < instead of <=. The formula still returns five neat characters, but a difference of exactly 500 becomes !: account 1300 would show ++!+. and the difference that sits right on the threshold is flagged as an exception.

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

Why it works

CONCAT accepts not only ranges but any array a formula produces, and joins it in order. That makes CONCAT the tool for collapsing a calculated array into one text.

The blank test comes first because an empty cell counts as 0 in a calculation, and ABS(0) <= 500 would silently give + for a month without a specification, which reads as reconciled.

Do not use this construction when the result needs a separator between items or has to skip blanks: TEXTJOIN("", TRUE, marks) does the same join and can skip empty items, and TEXTJOIN(", ", ...) adds separators.

Where the reader only needs to see the pattern, conditional formatting on B2:F5 is the alternative: it keeps the amounts visible, at the cost of not producing a value you can copy into the audit file or compare with another account.

More to watch out for

  • Drop the blank test and September shows + for every account, which reads as five reconciled months rather than a specification that has not arrived.
  • Leave out ABS and a large negative difference such as -640 is smaller than 500, so it shows + and a real exception goes unnoticed.
  • In Excel 2019, which has CONCAT but not dynamic arrays or LET, the variation without LET has to be confirmed with Ctrl+Shift+Enter, or IF evaluates only one cell.

Variations

=CONCAT(IF(B2:F2 = "", ".", IF(ABS(B2:F2) <= $H$2, "+", "!")))
=CONCAT(IF(B2:F2 = ""; "."; IF(ABS(B2:F2) <= $H$2; "+"; "!")))

The same without LET; shorter, but the range appears twice and must be changed in two places.

=TEXTJOIN("", TRUE, IF(B2:F2 = "", "", IF(ABS(B2:F2) <= $H$2, "+", "!")))
=TEXTJOIN(""; TRUE; IF(B2:F2 = ""; ""; IF(ABS(B2:F2) <= $H$2; "+"; "!")))

With TEXTJOIN the open months can be left out entirely: an empty item is skipped, so account 1300 shows ++++ with four characters.

Common mistakes

SymptomCauseFix
A date shows as 46127A date in the joined text shows up as a five-digit serial number such as 46127.Wrap the date in TEXT with a format code: TEXT(D2, "yyyy-mm-dd").
Words run together, no space between themCONCAT(A2:C2) returns FirstnameLastname with no space, or words run together.CONCAT never adds a separator. List the cells and put " " between them, or use TEXTJOIN(" ", TRUE, A2:C2) when every item needs the same separator.
Lookup on a key fails: 42 instead of 0042You build a key like 2026-0042 and the lookup on it fails, because the number part came out as 42.Leading zeros are display formatting, not part of the value. Use TEXT(B2, "0000") to write them into the text.
The joined digits cannot be added upYou expected CONCAT to give a number you can add up, because the pieces were all digits.The result is always text. Wrap it in VALUE if you really need a number, or keep the calculation separate from the label.