Spreadsheet Practice

How to combine a column into one cell with commas in Google Sheets

Google Sheets formula

=TEXTJOIN("; ",TRUE,B2:B8)

Use TEXTJOIN with TRUE to skip blanks: =TEXTJOIN("; ",TRUE,B2:B8) joins the emails into one line you can paste into the To field.

Result on the example
ana@ruiz.example; raj@patel.example; kim@ortiz.example; mo@diaz.example; sam@cole.example; eve@lund.example in D2
Excel
See below

See it work

The formula is already in D2. Change any input and the result updates, or edit the formula to experiment.

How it works

TEXTJOIN takes the separator, whether to ignore empty cells, and the range. With TRUE, Lee Chen's missing email is skipped instead of leaving "; ;" in the middle. The result updates when an address is added or changed.

=TEXTJOIN("; ",TRUE,B2:B8)

Result"ana@ruiz.example; raj@patel.example; kim@ortiz.example; mo@diaz.example; sam@cole.example; eve@lund.example"

  1. B2:B87 rows × 1 col: "ana@ruiz.example", "raj@patel.example", (empty), "kim@ortiz.example", …

    A range: 7 cells in one column.

  2. TEXTJOIN("; ",TRUE,B2:B8)"ana@ruiz.example; raj@patel.example; kim@ortiz.example; mo@diaz.example; sam@cole.example; eve@lund.example"

    TEXTJOIN: Joins text with a delimiter between items.

    • delimiter"; "
    • ignore_emptyTRUE
    • text1B2:B87 rows × 1 col: "ana@ruiz.example", "raj@patel.example", (empty), "kim@ortiz.example", …

In Excel

The same TEXTJOIN formula works in Excel 2019 and later.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=JOIN("; ",B2:B8)ana@ruiz.example; raj@patel.example; ; kim@ortiz.example; mo@diaz.example; sam@cole.example; eve@lund.exampleDifferent resultJOIN is Sheets' older function. It has no option to skip blanks. Has an empty slot, "; ;", where Lee Chen's email is missing.

Common mistakes

  • TEXTJOIN returns an error if the result is longer than 50,000 characters, the most a cell can hold.
  • Numbers keep their underlying value: a date joins as 46174. Format first with TEXT.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Combine a column into one cell with commas

In D2, join the email addresses in B2:B8 into one cell, separated by a semicolon and a space, skipping the missing one.

Questions

How do I join only the rows that match a condition?

Filter first: =TEXTJOIN(", ",TRUE,FILTER(A2:A8,B2:B8<>"")).

How do I put each item on its own line in the cell?

Use CHAR(10) as the separator and turn on text wrapping: =TEXTJOIN(CHAR(10),TRUE,A2:A8).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.