How do I add spaces or commas between items when using CONCATENATE?

Add a literal string containing the desired separator as its own argument, for example =CONCATENATE(A2, ", ", B2). Each separator is treated like any other text piece.

Can CONCATENATE handle numbers and dates without converting them first?

Yes. Numbers and dates are automatically coerced to text, but the default formatting may be undesirable. Use the TEXT function to specify exact number or date formats before concatenating.

Why does CONCATENATE sometimes return a #VALUE! error?

The most common cause is passing a range that contains more than one cell (e.g., A2:A5). CONCATENATE expects single-cell arguments, so it throws #VALUE!. Use individual cell references or switch to TEXTJOIN/CONCAT which accept ranges.

Is there a limit to how many arguments CONCATENATE can accept?

In Excel, CONCATENATE can handle up to 255 arguments, and the total length of the resulting string cannot exceed 32,767 characters. Google Sheets follows similar limits, but for very long strings consider using TEXTJOIN for better performance.

CONCATENATE function

CONCATENATE joins several text strings into one continuous string, in the order the arguments are supplied, and returns it as a single text value.

=CONCATENATE(text1, ...)

Generate a CONCATENATE formula

Describe what you need. The generator will reach for CONCATENATE where CONCATENATE is the right tool, and tell you when it is not.

Arguments

How CONCATENATE reads its arguments

Argument	Required	Description
text1	Required	The first piece of text to join; can be a string, number, cell reference, or formula that results in a value.
...	Repeating	Additional text arguments (text2, text3, …); each may be a literal, reference, or expression; empty arguments are ignored.

Returns

It returns a single text string.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Create a readable service note

Vehicle	Odometer	ServiceDate	Cost	Garage
Truck A	45230	2023-07-15	320.5	North Garage

=CONCATENATE(A2, " serviced on ", TEXT(C2, "yyyy-mm-dd"))

Result: Truck A serviced on 2023-07-15

The formula takes the vehicle name from A2, adds the literal phrase " serviced on ", and then formats the service date in C2 as a four-digit year, month, and day. CONCATENATE stitches these three pieces together in the exact order they appear, producing a human-readable sentence that can be placed in a report.

2. Build a formatted mileage-and-cost label

Vehicle	Odometer	ServiceDate	Cost	Garage
Truck A	45230	2023-07-15	320.5	North Garage

=CONCATENATE(A2, " – ", TEXT(B2, "#,##0"), " km – $", TEXT(D2, "0.00"))

Result: Truck A – 45,230 km – $320.50

Here A2 supplies the vehicle name, while TEXT formats the odometer reading with thousands separators and the cost with two decimal places. The literal strings " – " and " km – $" act as separators. CONCATENATE merges all parts, yielding a compact label suitable for a dashboard or printed tag.

3. Generate a unique garage-vehicle key

Vehicle	Odometer	ServiceDate	Cost	Garage
Truck A	45230	2023-07-15	320.5	North Garage

=CONCATENATE(E2, "|", A2)

Result: North Garage|Truck A

The garage name in E2 is concatenated with a pipe character ("|") and the vehicle name from A2. This creates a simple composite key that can be used for lookup tables or as a unique identifier in a database export.

Common errors

Which CONCATENATE error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	A multi-cell range such as A2:A4 is supplied as a single argument; CONCATENATE only accepts single-cell references.	Reference each cell individually (e.g., =CONCATENATE(A2, A3, A4)) or switch to TEXTJOIN/CONCAT which can handle ranges.
#REF!	One of the referenced cells has been deleted or the address is otherwise invalid, for example using Z1 after the column was removed.	Update the formula to point to a valid cell or remove the broken reference.
#N/A	An argument returns the error #N/A, such as a VLOOKUP that cannot find a match; CONCATENATE propagates that error.	Wrap the lookup in IFERROR (e.g., IFERROR(VLOOKUP(...), "")) to supply a fallback value before concatenation.

Tips and when to use something else

In newer Excel and Google Sheets, prefer CONCAT or TEXTJOIN; CONCAT accepts ranges directly, while TEXTJOIN lets you specify a delimiter and ignore blanks.
Combine CONCATENATE with TEXT to control number and date formatting, ensuring the final string looks exactly as you need.
Insert explicit separators (" ", ", ", "|", etc.) as separate arguments; an empty string ("") can be used to add spaces without extra characters.
When you need to join many cells with the same delimiter, TEXTJOIN is more efficient and produces cleaner formulas.

Related functions

CONCATCONCAT joins two or more text strings into one continuous string, automatically coercing numbers and dates to text without a delimiter.TEXTJOINTEXTJOIN merges multiple text strings or ranges into one string, using a chosen delimiter and optional ignoring of empty cells.LEFTLEFT returns the leftmost characters from a text string, optionally limited by a specified number of characters.VLOOKUPVLOOKUP searches the first column of a table and returns a value from a specified column in the same row, optionally using exact or approximate matching.TEXTTEXT converts a numeric, date, or time value into a formatted text string based on a custom format pattern you supply.

Frequently asked questions

How do I add spaces or commas between items when using CONCATENATE?: Add a literal string containing the desired separator as its own argument, for example =CONCATENATE(A2, ", ", B2). Each separator is treated like any other text piece.
Can CONCATENATE handle numbers and dates without converting them first?: Yes. Numbers and dates are automatically coerced to text, but the default formatting may be undesirable. Use the TEXT function to specify exact number or date formats before concatenating.
Why does CONCATENATE sometimes return a #VALUE! error?: The most common cause is passing a range that contains more than one cell (e.g., A2:A5). CONCATENATE expects single-cell arguments, so it throws #VALUE!. Use individual cell references or switch to TEXTJOIN/CONCAT which accept ranges.
Is there a limit to how many arguments CONCATENATE can accept?: In Excel, CONCATENATE can handle up to 255 arguments, and the total length of the resulting string cannot exceed 32,767 characters. Google Sheets follows similar limits, but for very long strings consider using TEXTJOIN for better performance.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator