CONCATENATE and & in Google Sheets: combine text from cells
To combine text from two cells in Google Sheets, join them with an ampersand or with the CONCATENATE function. Both give the same result. This guide shows how to add separators, keep the format of numbers and dates with TEXT, join a whole column with ARRAYFORMULA, and when JOIN or TEXTJOIN is the better tool.
To combine the text of two cells, put an ampersand between them, with a separator in quotes where you need one:
=B3 & " " & C3
CONCATENATE does the same as a function:
=CONCATENATE(B3, " ", C3)
With “John” in B3 and “Doe” in C3, both return “John Doe”. There is no difference in the result; the ampersand is shorter, and it is the one that works inside ARRAYFORMULA.
& |
CONCATENATE |
JOIN, TEXTJOIN |
|
|---|---|---|---|
| Joins | Any number of values | Any number of values | A whole range |
| Separator | Typed between the values | Typed between the values | One, set once |
| Row by row in ARRAYFORMULA | Yes | No, joins everything into one text | No, returns one value |
| Skips empty cells | No | No | TEXTJOIN can |
There is also CONCAT, which joins exactly two values; a third argument returns #N/A.
Reading to get this done?
Syntax
=value1 & value2 & ...
=CONCATENATE(string1, [string2, ...])
Each value is a text in quotes, a number, a cell or the result of another function. Nothing is added between the values: a space, a comma or a dash has to be written as a value of its own.
=CONCATENATE(B3, " ", C3, " earns $", D3)

=B3 & " " & C3 & " works in " & D3

Separators
Without a separator the values run together:
=CONCATENATE(B3, C3)

Add the separator as a text between them:
=CONCATENATE(B3, ", ", C3)

A hyphen between two columns makes a combined key, which is the usual way to prepare a lookup by two criteria:
=CONCATENATE(B3, "-", C3)

For a line break inside the cell, use CHAR(10) as the separator:
=CONCATENATE(B3, CHAR(10), C3, CHAR(10), D3)

You join text and a number in one cell, and the price loses its format or the date turns into a plain serial number.
Paste this into your AI assistant. It writes the ampersand and TEXT formulas that build labels from your columns, with numbers and dates kept in the format you want.
The prompt
Read https://www.owox.com/ai-agent-setup.md and follow it to set up OWOX Data Marts for me, starting from /blog/articles/concatenate-and-ampersand-google-sheets.md. The task: build me a label column from the name, amount and date columns in my sheet, keeping the currency and date formats, filled down the whole column. Tag the links you give me with utm_campaign=ai-task and utm_term=m3. Use what you know about my context and goals
Numbers and dates lose their format
Numbers and dates lose their format
Joining takes the value of a cell, not the way it is displayed. A salary shown as 55,000 becomes 55000, as in the first screenshot above, and a date becomes its serial number: 15 January 2023 turns into 44941. Wrap such values in TEXT and give the format you want.
A number with a text
=CONCATENATE("Q1 Budget: $", TEXT(B3, "#,##0"))

The same with the ampersand: ="Q1 Budget: " & TEXT(B3, "$#,##0"). For two decimals use "$#,##0.00", for a percentage "0%".
A date with a text
=CONCATENATE(B3, " joined on ", TEXT(C3, "mmmm dd, yyyy"))

The result is text
Whatever goes in, a text comes out. =1 & 2 returns “12” as text, which is left-aligned and ignored by SUM. To calculate with a joined number, convert it back: =VALUE(1 & 2).
A whole column: use the ampersand
A whole column: use the ampersand
Inside ARRAYFORMULA the two forms behave differently. The ampersand joins row by row:
=ARRAYFORMULA("Employee: " & B3:B10 & " - Active")

CONCATENATE takes the ranges as lists of values and joins all of them into a single text:
=ARRAYFORMULA(CONCATENATE(B3:B10, " - ", C3:C10))

Every name, then the separator once, then every department. For one result per row, write the formula with ampersands. See array formulas.
Empty rows can be skipped with IF:
=ARRAYFORMULA(IF(LEN(B3:B10) > 0, "Employee: " & B3:B10 & " - Present", "Missing Data"))

Join a range: JOIN and TEXTJOIN
Join a range: JOIN and TEXTJOIN
To merge many cells with the same separator, such as a column into a comma-separated list, listing every cell with ampersands is the wrong tool. TEXTJOIN takes the range and the separator once and can skip empty cells:
=TEXTJOIN(", ", TRUE, B3:B10)
See the guide to JOIN and TEXTJOIN.
Text that depends on a value
A joined text can contain the result of any function. IF chooses a wording:
=CONCATENATE(B3, " has ", IF(C3>=85, "met or exceeded", "not met"), " their target.")

Totals and dates work the same way, with TEXT for the format:
="Report generated on " & TEXT(TODAY(), "mmmm d, yyyy") & ". Total: " & TEXT(SUM(C3:C10), "$#,##0")
Errors and wrong results
⚠️ Error: A date appears as a number such as 44941.
✅ Solution: Wrap the date in TEXT: TEXT(C3, "mmm d, yyyy").
⚠️ Error: A number loses its thousands separator, currency sign or percent sign.
✅ Solution: The format is not part of the value. Use TEXT with the format code.
⚠️ Error: Two spaces, or a space at the end of the result.
✅ Solution: One of the cells is empty and its separators remain. Wrap the formula in TRIM, or use TEXTJOIN with TRUE.
⚠️ Error: ARRAYFORMULA with CONCATENATE returns one long text.
✅ Solution: Replace CONCATENATE with ampersands.
⚠️ Error: #N/A in CONCAT.
✅ Solution: CONCAT takes exactly two values. Use the ampersand or CONCATENATE for more.
⚠️ Error: The formula returns an error that is in one of the cells.
✅ Solution: An error in any joined value becomes the result. Fix the cell, or wrap that value in IFERROR.
⚠️ Error: The joined key does not match in a lookup.
✅ Solution: The two sides were built differently: another separator, a number against a text, or a stray space. Build both keys with the same formula.
Related Google Sheets functions
Related Google Sheets functions
- JOIN and TEXTJOIN: Join a range with one separator.
- TEXT: Format codes for numbers and dates.
- How to split cells: The reverse of joining.
- CHAR: Line breaks and symbols as separators.
- VLOOKUP with multiple criteria: Joined keys in lookups.
- QUERY with CONCATENATE: Joined columns in a query.
- LEFT, RIGHT and MID: Take a text apart by position.
Labels are built in the sheet, the numbers inside them are not
Labels are built in the sheet, the numbers inside them are not
A sentence such as “Total: $578,000 as of October 6” is assembled by a formula, and it reads as a fact. The formula only knows the cells it points to. If they hold last month’s export, the sentence is confidently wrong, and joined text is the part of a report that people copy into emails and slides.
The OWOX Data Marts extension for Google Sheets covers that half. A data analyst publishes a data mart (a table or an SQL query in your data warehouse) and makes it available for reports. In Sheets you open the extension, pick the data mart, tick the columns you want and run it. The rows land in a tab, ready for your formulas.

What changes for the person who maintains the sheet:
- Refresh replaces re-export. Refresh current report in the extension menu re-runs the same pull, and a scheduled refresh keeps the tab current without anyone touching it.
- The source is written on the sheet. The header cell of an imported tab carries a note with the data mart it came from, the time of the import and a link to the report.
- No SQL and no warehouse access needed. The analyst decides which data marts and columns are available. You choose from that list.
The extension does not write or change the analyst’s SQL. The labels your formulas assemble then quote the refreshed numbers. The extension comes with the OWOX Cloud plans of OWOX Data Marts; spreadsheet reporting describes both ways data reaches a sheet.
You join text and a number in one cell, and the price loses its format or the date turns into a plain serial number.
Paste this into your AI assistant. It writes the ampersand and TEXT formulas that build labels from your columns, with numbers and dates kept in the format you want.
The prompt
Read https://www.owox.com/ai-agent-setup.md and follow it to set up OWOX Data Marts for me, starting from /blog/articles/concatenate-and-ampersand-google-sheets.md. The task: build me a label column from the name, amount and date columns in my sheet, keeping the currency and date formats, filled down the whole column. Tag the links you give me with utm_campaign=ai-task and utm_term=m3. Use what you know about my context and goals
Prefer to start on your own? Get started free
Frequently Asked Questions
Is there a limit to the number of text strings that can be combined using the CONCATENATE function?
CONCATENATE accepts as many values as you list, and the practical limit is the size of the result: it has to fit into one cell. To join a whole range with one separator, TEXTJOIN is the better function: it takes the range instead of a list of cells and can skip the empty ones.
What are the key differences between the & operator and the CONCATENATE function?
They return the same result: =A1 & " " & B1 and =CONCATENATE(A1, " ", B1) both join two cells with a space. The difference shows inside ARRAYFORMULA: the ampersand joins row by row, while CONCATENATE joins all the values of the ranges into one text. For that reason, and because it is shorter, the ampersand is the usual choice.
Can I add spaces or custom delimiters when using the & or CONCATENATE function?
Yes, you can add spaces or custom delimiters with both the & operator and the CONCATENATE function. Use quotation marks to include spaces or delimiters. For example, =A1 & ", " & B1 or =CONCATENATE(A1, ", ", B1) adds a comma and space between the combined text.
What is the syntax for the CONCATENATE function in Google Sheets?
The syntax for the CONCATENATE function in Google Sheets is CONCATENATE(string1, [string2, ...]). Replace string1, string2, etc., with the text, numbers, or cell references you want to combine. For example, =CONCATENATE(A1, " ", B1) joins text from cells A1 and B1 with a space in between.
How can I combine text from two or more cells in Google Sheets using the & operator?
To combine text from two or more cells in Google Sheets using the & operator, use a formula like =A1 & " " & B1. Replace A1 and B1 with your cell references and " " with your desired separator. This joins the text from the specified cells.
