Google Sheets CONCATENATE formula: join cells, columns or a whole range with a comma, space or new line

Say which cells to join and what goes between them. Copy the formula. You can join a few cells in one row, join everything in a range into one cell, or join two or more whole columns row by row. It runs in your browser and nothing you type here is sent anywhere. Every formula below was run in a real Google Sheet on 3 November 2026 (blank cells, numbers, dates, quotes, new lines, a long separator) once in a US-locale spreadsheet and once in a German-locale one with semicolons: all checks matched.

How to use it

Paste the formula into one empty cell. The row by row choice spills down, so the cells below it must be empty, or Sheets shows a #REF! error. Sheets has no CONCATENATE button; the & sign does the same job and so does TEXTJOIN.

Which one to pick

The catches (found by testing in a real Sheet)

• A blank cell still gets its separator. A2 & "-" & C2 & "-" & B2 with C2 empty gave Ann--Lee. Tick "skip blank cells" and the builder uses TEXTJOIN, which gave Ann-Lee.
• TEXTJOIN with a range keeps blanks unless you say so. Its second argument decides: FALSE gave Ann-Lee- for three cells with the last one empty, TRUE gave Ann-Lee.
• A blank row in a whole column. Joining columns with & gives a lone separator (a space, a dash) for every empty row all the way down. The builder wraps it in IF so empty rows stay empty. The same goes for a block of columns joined with TEXTJOIN and blanks kept: without a check it returned - in every empty row below your data; the builder adds the check there too.
• A partly blank row. With "skip blank cells" off, a row with only the second column filled gave Kim (a space first). With it on, it gave Kim.
• Numbers join as plain numbers. Ann and 42 gave Ann 42.
• Dates join as serial numbers. A date cell joined to text gave Ann 46329, not a date, in both the US and the German sheet. Wrap the date cell in TEXT(D2, "yyyy-mm-dd") before you join it (see the TEXT help linked below).
• The separator can be more than one character. A separator of & gave Ann & Lee. A double quote works (the builder doubles it in the formula). \n becomes CHAR(10), a new line inside the cell; turn on "wrap text" to see it.
• Extra spaces. Tick "cut extra spaces" to wrap the result in TRIM: a blank in the middle then gave Ann Lee with a single space.
• Decimal comma. In a German-locale sheet the same formula needs semicolons between arguments; pick the semicolon separator.

No code: KeepSorted

The add-on sorts the original table in place without code: no second copy, so formatting, checkboxes and notes move with their rows and people keep editing the same table. Select the range, pick the sort columns, Save. Free: one area per tab, up to 2 sort columns, no trial clock. Pro ($5/month or $39/year): up to 5 sort columns, custom orders like High > Medium > Low, ticked checkbox rows to the bottom, stacked tables sorted separately, and sorting after form responses, other editors' changes and an hourly check.

More KeepSorted tools and guides

• SPLIT formula builder (text to columns or rows)
• VLOOKUP formula builder (XLOOKUP, INDEX MATCH, whole column)
• COUNTIF / SUMIF formula builder (count, add up, repeats)
• Running total and percent-of-total formula builder
• RANK formula builder (ties, rank within a group)
• FILTER formula builder (text, number, date conditions)
• Unique values formula maker (UNIQUE, counts, duplicates)
• Sort formula generator (SORT, SORTN, QUERY order by)
• Auto sort script generator (Apps Script that sorts the original)
• StaySorted alternative: an honest comparison

Sources

• Google Docs Editors Help: CONCATENATE
• Google Docs Editors Help: TEXT (number and date formats)
• Google Docs Editors function list (TEXTJOIN, BYROW, HSTACK, ARRAYFORMULA, TRIM, CHAR)