Google Sheets Tips

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.

Google Sheets TipsVadym Kramarenko7 min read

CONCATENATE and & in Google Sheets: combine text from cells

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

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)

Google Sheets with first name, last name and salary in columns B to D and the formula =CONCATENATE(B3, “ “, C3, “ earns $”, D3) returning John Doe earns $55000

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

Google Sheets with first name, last name and department and the formula =B3 & “ “ & C3 & “ works in “ & D3 returning John Doe works in Marketing

Separators

Separators

Without a separator the values run together:

=CONCATENATE(B3, C3)

Google Sheets with the formula =CONCATENATE(B3, C3) returning JohnDoe, the two names with nothing between them

Add the separator as a text between them:

=CONCATENATE(B3, ", ", C3)

Google Sheets with the formula =CONCATENATE(B3, “, “, C3) returning John, Doe

A hyphen between two columns makes a combined key, which is the usual way to prepare a lookup by two criteria:

=CONCATENATE(B3, "-", C3)

Google Sheets with names in column B and numbers in column C and the formula =CONCATENATE(B3, “-”, C3) returning John-85

For a line break inside the cell, use CHAR(10) as the separator:

=CONCATENATE(B3, CHAR(10), C3, CHAR(10), D3)

Google Sheets with the formula =CONCATENATE(B3, CHAR(10), C3, CHAR(10), D3) returning the name, department and project on three lines in one cell

Hand this to your AI

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

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

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"))

Google Sheets with amounts in column B and the formula =CONCATENATE(“Q1 Budget: $”, TEXT(B3, “#,##0”)) returning Q1 Budget: $10,000

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"))

Google Sheets with names and join dates and the formula =CONCATENATE(B3, “ joined on “, TEXT(C3, “mmmm dd, yyyy”)) returning John joined on January 15, 2023

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")

Google Sheets with names in B3:B10 and the formula =ARRAYFORMULA(“Employee: “ & B3:B10 & “ - Active”) returning one label for each row

CONCATENATE takes the ranges as lists of values and joins all of them into a single text:

=ARRAYFORMULA(CONCATENATE(B3:B10, " - ", C3:C10))

Google Sheets with the formula =ARRAYFORMULA(CONCATENATE(B3:B10, “ - “, C3:C10)) returning one long text: all the names run together, a dash, then all the departments run together

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"))

Google Sheets with two empty name cells and a formula with ARRAYFORMULA, IF and LEN returning Missing Data for those rows and a label for the others

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

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.")

Google Sheets with names and target percentages and a CONCATENATE formula with IF returning sentences such as John has met or exceeded 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

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

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.

Google Sheets with the OWOX Data Marts extension open beside an ad spend table: the Unified Ad Spend data mart, nine of fourteen columns ticked and a Save & Run button – the rows in the sheet come from a governed data mart, not a pasted export

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.

Hand this to your AI

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

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

FAQ

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.

Who wrote this

Vadym Kramarenko

Vadym Kramarenko Growth Marketing Manager

Vadym Kramarenko is a Growth Marketing Manager at OWOX, where he drives user acquisition and product-led growth strategies. He hosts the OWOX podcast, interviewing analytics professionals about data-driven marketing, attribution, and reporting best practices. Vadym specializes in turning complex analytics concepts into practical, actionable marketing frameworks.