The formula
Join, skip blanksExcelGoogle Sheets
=TEXTJOIN(", ",TRUE,A2:A5)In D2 → red, large, cotton
Join only where B = "Y"Excel
=TEXTJOIN(", ",TRUE,IF(B2:B5="Y",A2:A5,""))In D3 → red, cotton
Google Sheets (with condition)Google Sheets
=TEXTJOIN(", ",TRUE,FILTER(A2:A5,B2:B5="Y"))In D4 → red, cotton
Any Excel (manual)ExcelGoogle Sheets
=A2&", "&A4&", "&A5In D5 → red, large, cotton
✓ Tested. All 3 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown. 1 result for formulas tagged Google Sheets was checked with a reference implementation of the documented behavior, because LibreOffice can't run that function.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Tag | Use? | ||
| 2 | red | Y | red, large, cotton | |
| 3 | Y | red, cotton | ||
| 4 | large | N | red, cotton | |
| 5 | cotton | Y | red, large, cotton |
How it works
- ", "
- Delimiter. Use CHAR(10) for a line break (turn on Wrap Text).
- TRUE
- Ignore empty cells.
- IF(B2:B5="Y",A2:A5,"")
- Only include tags where Use? is Y.
Common errors and fixes
Why does the formula return #VALUE!?
Result longer than 32,767 characters.
Line breaks don't show?
Turn on Wrap Text for the cell.
Does this work in Google Sheets?
Yes. Use the formulas tagged "Google Sheets" on this page; formulas tagged only "Excel" use Excel-specific syntax.