Using Concatenate Range

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:

delimiter:
An optional text string that will separate the concatenated items. For example, a comma (,).

ignore_empty:
If TRUE (the default), empty cells are ignored.

use_formats:
If TRUE, cell values in the concatenation will be formatted.
If FALSE (the default), no formatting is applied.

text1:
Text item to be joined. It can be a text string, or array of strings, such as a range of cells.

[text2, …]:
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:

That’s it!

Having trouble? Click here.