Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
Fix the CONCAT error
Excel & Google SheetsWhat CONCAT means
When CONCATENATE (or the & operator) is not working, the result is either an error, the values are joined without the expected separator, numbers are joined with wrong formatting (e.g. dates appearing as serial numbers), or the formula shows as text rather than computing.
Common causes
- No separator between joined values — =CONCATENATE(A2,B2) joins with no space; a space literal " " must be added explicitly between arguments.
- A date or number is joined directly and appears as its underlying serial number or unformatted number (e.g. 45658 instead of 01/01/2025) because concatenation converts values to their raw representation.
- An argument references an entire column or a multi-cell range — CONCATENATE accepts single cells only; a range reference causes #VALUE!.
- The cell is formatted as Text, so the formula itself is rendered as a string instead of being evaluated.
- Using CONCATENATE in older Google Sheets syntax or CONCAT (which only accepts two arguments in some versions) instead of the & operator or TEXTJOIN.
Example fix
Broken
=CONCATENATE(A2,B2,C2)Fixed
=CONCATENATE(A2," ",TEXT(B2,"dd/mm/yyyy")," ",C2)Added a space literal between each value and used TEXT() to format B2 as a readable date rather than a serial number before concatenation.
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
How to fix it
- 1Add separators explicitly: =CONCATENATE(A2," ",B2) or =A2&" "&B2 to join with a space between values.
- 2Format numbers and dates before joining using TEXT(): =A2&" "&TEXT(B2,"dd/mm/yyyy") for dates, or TEXT(C2,"$#,##0.00") for currency.
- 3Use single-cell references only. To join a range, use TEXTJOIN in Excel 2019+ and Google Sheets: =TEXTJOIN(", ",TRUE,A2:A10) joins the range with a comma, skipping blanks.
- 4If the cell shows the formula as text, change the cell format to General (not Text) and press F2 then Enter to re-evaluate.
- 5Prefer & over CONCATENATE for conciseness: =A2&" "&B2 is equivalent and easier to read. Use TEXTJOIN for ranges.
Stop hunting errors by hand.
Upload your spreadsheet and the Auditor flags every CONCAT and broken formula at once — or paste this one formula and get the fix explained.
Frequently asked
What is the difference between CONCATENATE, CONCAT, and TEXTJOIN?
CONCATENATE accepts up to 255 individual cell references. CONCAT (Excel 2019+, Sheets) is a shorter alias that also accepts ranges. TEXTJOIN (Excel 2019+, Sheets) adds a delimiter and can skip blanks — it is the best choice for joining a range of cells.
How do I add a line break inside a concatenated string?
Use CHAR(10) in Excel and Sheets: =A2&CHAR(10)&B2. Make sure the cell has Wrap Text enabled (Home > Wrap Text in Excel, Format > Text wrapping in Sheets) so the line break is visible.
My concatenated date shows as a number like 45658 — how do I fix it?
Concatenation strips date formatting and shows the underlying serial number. Wrap the date reference with TEXT(): =A2&" "&TEXT(B2,"d mmm yyyy") to display it in a readable format.