Practice free

ARRAYTOTEXT function

ARRAYTOTEXT flattens a range or array into one piece of text. It is handy for showing what a spilled result contains, or for pasting a list into a single cell.

Example

Read it as a sentence: turn the cells in B1:C2 into one line of text, with the values separated by commas.

Practice this in the app

Syntax

ARRAYTOTEXT(array, [format])

Arguments in [square brackets] are optional.

array (required)
The range or array to turn into text.
format (optional)
0 gives a readable comma-separated list (default); 1 gives strict format with quotes and braces, like an array constant.

Returns: A single text string describing the array contents.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=ARRAYTOTEXT({1,"a";TRUE,2}) 1, a, TRUE, 2 Concise
=ARRAYTOTEXT({1,"a";TRUE,2},1) {1,"a";TRUE,2} Strict
=ARRAYTOTEXT(5) 5 Scalar
=ARRAYTOTEXT(Items!B1:C2) Item, Category, Boxer cartridge, Tools Range
=ARRAYTOTEXT(Data!F1:F5) , 123, 45351, TRUE, #DIV/0! Blank, text number, date, bool, error
=ARRAYTOTEXT({1,2},2) #VALUE! Bad format

The Items sheet these formulas read

The Data sheet these formulas read

A cell that starts with = holds that formula.

Watch out for

  • Strict format (1) wraps text in quotes and uses semicolons between rows.
  • Concise format hides the difference between numbers and text that look alike.

Where it works

Excel
ARRAYTOTEXT arrived in Excel 2021. Older versions do not have it.
Google Sheets
No direct equivalent. Use TEXTJOIN(", ",TRUE,range) for a similar result.

Part of Text functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .