MS Excel: ARRAYTOTEXT function for single text string

In Excel's dynamic formula environment, it’s often necessary to convert arrays into plain text—especially for documentation, exporting, or debugging. The ARRAYTOTEXT function provides a convenient way to do exactly that: it turns array values into a single text string, either in a readable or strict format.

Introduced in Excel 365 and Excel 2019, ARRAYTOTEXT is particularly useful when working with dynamic arrays, spilled ranges, or formulas that return multiple results.

Syntax

=ARRAYTOTEXT(array, [format])

Arguments:

Argument	Description
array	The range or array you want to convert to text
format	Optional. Determines how the result is formatted: 0 (default, concise) or 1 (strict)

Format Options

Format	Description
0 or omitted	Concise format — text is joined in a simple, human-readable format
1	Strict format — returns values exactly as they appear in formulas (including quotation marks and full structure)

Examples

Example 1: Simple Horizontal Array

=ARRAYTOTEXT({1,2,3})

Returns: "1, 2, 3"

Example 2: Vertical Array with Strict Format

=ARRAYTOTEXT({1;2;3}, 1)

Returns: {"1";"2";"3"}Strict mode shows array structure, semicolons for rows.

Example 3: Converting a Range to Text

If A1:A3 contains:

Apple Banana Cherry

Then:

=ARRAYTOTEXT(A1:A3)

Returns: "Apple, Banana, Cherry"

=ARRAYTOTEXT(A1:A3, 1)

Returns: {"Apple";"Banana";"Cherry"}

Use Cases

Scenario	Description
📄 Logging formulas	Convert array results to text for notes or explanations
📤 Exporting data	Convert multi-cell outputs into a single text value
🧪 Debugging	See the exact structure of array formulas
🔁 Combine with TEXTJOIN	Create custom-formatted text strings from arrays

Things to Remember

• Array size matters — large arrays may produce very long strings.

• ARRAYTOTEXT doesn’t work in older versions of Excel (prior to 2019/365).

• If referencing a spilled range, make sure it’s not volatile or circular.

Summary

Feature	Details
Function Name	ARRAYTOTEXT
Purpose	Converts array values to a text string
Format Options	0 (concise), 1 (strict)
Available In	Excel 365, Excel 2019 and later
Common Use Cases	Exporting, debugging, text reporting

Related Functions

Function	Use When
TEXTJOIN	Join values with a custom delimiter
TEXTRANGE (planned)	Similar to ARRAYTOTEXT, but more formatting control
VALUE	Convert text back to numbers
TEXT	Format individual values
LET + ARRAYTOTEXT	Useful for clean formula documentation

Final Thoughts

ARRAYTOTEXT is a simple but powerful utility function in Excel’s modern toolkit. Whether you're building dynamic models or just need to cleanly present your results, it helps turn complex arrays into clear, readable text output.

Combine ARRAYTOTEXT with LET to create formulas that document themselves in a readable way.