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

How to use Concatenate function for Excel: formula and results
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.

Get started with XLTools ribbon
– free trial gives you 14 days of full access to all features.

Syntax

=XL.Concatenate(range, separator, format)
ArgumentDescription
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.


FormulaDescription
=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?
How can we help?