XL.Concatenate function: concatenate values in a range with any separator

XL.Concatenate function – Text function type – сoncatenates all values in a range into a single string, optionally with any delimiter and qualifiers.
Before you begin, add Functions to Excel
Functions are one of the 20+ features within XLTools Add-in for Excel. Works in Excel 2019, 2016, 2013, 2010, desktop Office 365.

Download XLTools for Excel
Syntax
=XL.Concatenate(range, separator, format)
Argument | Description |
---|---|
range [required] | The range of cells which values you want to concatenate |
separator [optional] | The text string representing the separator between values |
format [optional] | The qualifiers which should be applied to each particular value before concatenation |
Examples [download]
Download this spreadsheet with examples and try the function yourself.
Download examples with this function (xlsx)
Formula | Description |
---|---|
=XL.Concatenate(A2:A6) | Concatenate all values in the range A2:A6 |
=XL.Concatenate(A2:A6, ", ") | Concatenate all values in the range A2:A6 with the comma-space delimiter (, ) |
=XL.Concatenate(A2:A6, ", ", "'{0}'") | Concatenate all values in the range A2:A6 a with the comma-space delimiter (, ), add single quotes as a qualifier ('{0}') |
Any questions or suggestions?