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.
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.
| 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?
