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, optionally with a delimiter and qualifiers.

The function helps concatenate values in an entire range into a single string, set up any custom delimiter and add qualifiers to the concatenated values.

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?