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.


=XL.Concatenate(range, separator, format)

The range of cells which values you want to concatenate
The text string representing the separator between values
The qualifiers which should be applied to each particular value before concatenation

Examples [download]

Download this spreadsheet with examples and try the function yourself.

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