1. Open your Excel workbook.
2. In your desired cell, type the following formula:
=CONCATENATERANGE(delimiter, ignore_empty, use_formats, text1, [text2], …)
Note: Excel will not show this formula, because it is a custom formula.
The arguments of the formula are as follows:
An optional text string that will separate the concatenated items. For example, a comma (,).
If TRUE (the default), empty cells are ignored.
If TRUE, cell values in the concatenation will be formatted.
If FALSE (the default), no formatting is applied.
Text item to be joined. It can be a text string, or array of strings, such as a range of cells.
Additional text items to be joined. Each can be a text string, or array of strings, such as a range of cells.
EXAMPLE #1 – Concatenating ranges of cells with formatting
If we have three cell ranges, A1:B2, D3 and E3 with the following values:
we can concatenate these values, separated by a comma, and retain their formatting, as follows:
EXAMPLE #2 – Concatenating ranges of cells without formatting
To concatenate the values from EXAMPLE #1 without formatting, we change the formula as follows:
Having trouble? Click here.