ARRAYTOTEXT Function

The ARRAYTOTEXT function converts an array or range to a text string in a specific format that shows all values. Values are separated by commas (,) or semicolons (;), depending on the format requested and the structure of the array. 

The ARRAYTOTEXT function takes two arguments: array and format. Array is the array or range to convert to text. Array can be provided as a range like A1:A3 or an array like {1,2,3}. The optional format argument controls the structure of the output. There are two formats available, concise and strict:

• When format is zero (0), ARRAYTOTEXT will return a concise format. Essentially, the concise format is a plain, human-readable format like: red, blue, green. The delimiter between items will be a comma, text values are not in quotes, and curly braces are absent. ARRAYTOTEXT will output the concise format by default.
• When format is set to 1, ARRAYTOTEXT will output a strict format like: {"red","blue","green"}. The text will appear in curly braces {}, and delimiters will follow the structure of the array provided, with semicolons (;) separating rows and commas (,) separating columns.

The strict format mirrors what you see in the formula bar if you select a range and press F9. In fact, you can paste the strict output format directly into the formula bar, and Excel will return values to the worksheet in a range that follows the original structure of the array. For example, this formula will return a range with 6 cells (3 rows, and 2 columns):

={"red",1;"blue",2;"green",3}

In other words, you can use ARRAYTOTEXT with the strict format to get a text representation of a range in Excel that can be used in a formula like a range or array.

Note from Dave: Since you can do the same thing with the F9 key, I'm unsure how helpful this is. Please let me know if you discover a good use case!

Examples

With the values 1, 2, and 3 in cells A1:A3:

=ARRAYTOTEXT(A1:A3) // returns "1, 2, 3"
=ARRAYTOTEXT(A1:A3,1) // returns "{1;2;3}"

Notice both results are text values, but in the second example, values are separated by semicolons, and the output is enclosed in curly braces.

The strict format option will also wrap text values in double quotes (""). For example, with "red", "blue", and "green" in A1:A3:

=ARRAYTOTEXT(A1:A3) // returns "red, blue, green"
=ARRAYTOTEXT(A1:A3,1) // returns "{"red";"blue";"green"}"

In the example shown above, the formula in E11 refers to a range that contains both text values and numbers:

=ARRAYTOTEXT(B14:C16) // returns "Red, 3, Green, 2, Purple, 1"
=ARRAYTOTEXT(B14:C16,1) // returns "{"Red",3;"Green",2;"Purple",1}"

Notice only the text values in the second formula are enclosed in double quotes. In addition, rows are separated by semicolons and columns are separated by commas, following the structure of arrays in Excel.