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 2024, 2019, 2016, 2013, 2010, and Microsoft 365.
Syntax
| 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 with the comma-space delimiter (, ), add single quotes as a qualifier ('0') |
Any questions or suggestions?
