Excel functions / Text
ARRAYTOTEXT function
All the values of a range written out as one piece of text.
Syntax
=ARRAYTOTEXT(array, [format])
Arguments
array: The range or array.format(optional): 0 (default): values separated by commas. 1: the way an array is typed in a formula, {1,2;3,4}.
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
A column as a list
A B C 1 Item 2 Pen Pen, Stapler, Folder 3 Stapler 4 Folder In C2:
=ARRAYTOTEXT(A2:A4)→ Pen, Stapler, FolderAs an array constant
A B C 1 Item 2 Pen {"Pen";"Stapler";"Folder"} 3 Stapler 4 Folder In C2:
=ARRAYTOTEXT(A2:A4, 1)→ {"Pen";"Stapler";"Folder"}
Common mistakes
- Wanting another separator. ARRAYTOTEXT always uses a comma and a space. For a different separator, or to skip empty cells, use TEXTJOIN.
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.
- Danish: MATRIXTILTEKST
- Finnish: MATRIISI.TEKSTIKSI
- French: TABLEAU.EN.TEXTE
- German: MATRIXZUTEXT
- Hungarian: TÖMB.SZÖVEGGÉ
- Italian: MATRICE.A.TESTO
- Norwegian: MATRISETILTEKST
- Polish: TABLICA.NA.TEKST
- Russian: МАССИВВТЕКСТ
- Spanish: MATRIZATEXTO
- Swedish: MATRISTILLTEXT
- Turkish: DİZİMETİN
Try ARRAYTOTEXT in the playground
Related functions
- TEXTJOIN: Joins text with a separator between each item.
- VALUETOTEXT: Any value as text. Text passes through unchanged, or in quotes in the strict format.
- CONCAT: Joins text from several values or ranges.