Excel functions / Text

ARRAYTOTEXT function

All the values of a range written out as one piece of text.

Syntax

=ARRAYTOTEXT(array, [format])

Arguments

When to use it

ARRAYTOTEXT joins the values of a range into one text, separated by commas. With 1 as the second argument it writes the range as an array constant, with braces, quotes around text and semicolons between rows, ready to paste into a formula.

Examples

  1. A column as a list

    ABC
    1Item
    2PenPen, Stapler, Folder
    3Stapler
    4Folder

    In C2: =ARRAYTOTEXT(A2:A4) → Pen, Stapler, Folder

    Try this example

  2. As an array constant

    ABC
    1Item
    2Pen{"Pen";"Stapler";"Folder"}
    3Stapler
    4Folder

    In C2: =ARRAYTOTEXT(A2:A4, 1) → {"Pen";"Stapler";"Folder"}

    Try this example

Common mistakes

ARRAYTOTEXT in other languages

Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.

Translate a whole formula

Try ARRAYTOTEXT in the playground

Related functions