How to Concatenate Cells with a Separator in Excel?
Concatenate with Separator is a Excel function that combines text from multiple cells into one cell, using a specified delimiter and ignoring blank cells.. Formula Genius generates and validates this formula automatically from a plain-English prompt.
Easily combine values from different cells into one, using a specified separator while ignoring any empty cells.
The Formula
"Join values from multiple cells into one cell with a comma, dash, or space separator, skipping blank cells automatically"
=TEXTJOIN(", ", TRUE, A1:A5)This formula combines text from multiple cells into one cell, using a specified delimiter and ignoring blank cells.
Step-by-Step Breakdown
- TEXTJOIN is the function used for concatenation with a separator.
- The first argument is the delimiter, which can be a comma, dash, or space.
- The second argument is TRUE, which tells Excel to ignore empty cells.
- The third argument is the range of cells you want to concatenate.
Edge Cases & Warnings
- If all cells in the range are blank, the result will be an empty string.
- Using a non-existent range will return an error.
- If the delimiter is an empty string, the values will be concatenated without any separation.
Examples
"A1: 'Apple', A2: '', A3: 'Banana', A4: 'Cherry'"
'Apple, Banana, Cherry'"B1: 'Dog', B2: 'Cat', B3: '', B4: 'Fish'"
'Dog, Cat, Fish'Frequently Asked Questions
Can I use different separators?
Yes, you can change the delimiter to any character you prefer.
What happens if I include only blank cells?
The result will be an empty string.
Is TEXTJOIN available in all Excel versions?
TEXTJOIN is available in Excel 2016 and later versions.
Can't find what you need?
Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.