Text

ARRAYTOTEXT Function in Excel

Returns an array of text values from any specified range.

Syntax

  • =ARRAYTOTEXT(array, [format])

Arguments

  • array (required): Array to convert
  • format (optional): 0=concise, 1=strict

Examples

  • =ARRAYTOTEXT(A1:B2) - Convert array to text - Result: Text representation

Excel ARRAYTOTEXT function documentation

  • ARRAYTOTEXT returns text representing an array or range — Microsoft 365.
  • Syntax: =ARRAYTOTEXT(array, [format]). Useful for displaying spill results as readable strings.
  • Related: [VALUETOTEXT](/functions/valuetotext/), [TEXTJOIN](/functions/textjoin/), [CONCAT](/functions/concat/).

ARRAYTOTEXT syntax

  • array: range or spill reference.
  • format 0 concise, 1 strict — similar to VALUETOTEXT rules.
  • Joins array elements with list separator for export/debug.
  • Works on 1D and 2D arrays with defined flatten behavior.

Step-by-step: debug FILTER spill as text

  • Step 1 — =FILTER(A2:C100, B:B="East") spills results.
  • Step 2 — =ARRAYTOTEXT(spill_range) in audit cell.
  • Step 3 — Compare before/after formula changes.
  • Step 4 — Prefer TEXTJOIN for custom delimiters in production labels.
  • Step 5 — Log ARRAYTOTEXT output in change documentation.

ARRAYTOTEXT vs TEXTJOIN vs CONCAT

  • ARRAYTOTEXT — automatic array stringification.
  • TEXTJOIN — delimiter control + ignore blanks.
  • CONCAT — range join without separator default.
  • Text functions hub: [/categories/text/](/categories/text/).

Worked examples to copy

  • Spill debug: =ARRAYTOTEXT(A2#).
  • Strict: =ARRAYTOTEXT(range, 1).
  • Nested: =ARRAYTOTEXT(UNIQUE(A:A)).
  • Message: ="Results: "&ARRAYTOTEXT(results).

People also ask

  • ARRAYTOTEXT Excel 2019? — No; 365.
  • ARRAYTOTEXT 2D arrays? — Flattens per Excel rules with separators.
  • ARRAYTOTEXT vs clipboard? — Formula-driven reproducible text.
  • Large arrays? — May hit string length limits.

Common errors

  • Requires Excel 365

Use cases

  • Array display
  • Debugging
  • Data export

Frequently asked questions

  • What is ARRAYTOTEXT used for? ARRAYTOTEXT converts an array or range to a text string, showing all values. It's useful for debugging formulas, displaying array contents in a single cell, or creating text representations of data for export or documentation.
  • What's the difference between format 0 and 1? Format 0 (concise): values separated by commas, like '1, 2, 3'. Format 1 (strict): includes braces and semicolons for rows, like '{1, 2; 3, 4}'. Use concise for display; use strict when you need to recreate the array structure.
  • How do I display a dynamic array result as text? Wrap your array formula: =ARRAYTOTEXT(UNIQUE(A:A)). This shows all unique values as a comma-separated string in one cell, useful for quick review or when you need the result as text rather than spilled values.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use ARRAYTOTEXT

  • Array display — common Text scenario for ARRAYTOTEXT.
  • Debugging — common Text scenario for ARRAYTOTEXT.
  • Data export — common Text scenario for ARRAYTOTEXT.

ARRAYTOTEXT in the Text category

  • Browse all Text functions at /categories/text/ for related formulas.
  • ARRAYTOTEXT syntax: =ARRAYTOTEXT(array, [format])
  • array (required): Array to convert
  • format (optional): 0=concise, 1=strict
  • Confirm ARRAYTOTEXT arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm ARRAYTOTEXT arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • VALUETOTEXT (/functions/valuetotext/): Returns text from any specified value.
  • TEXTJOIN (/functions/textjoin/): Joins text from multiple ranges with a delimiter, with option to ignore empty cells.
  • CONCAT (/functions/concat/): Joins two or more text strings into one string.

Errors to watch for

  • Requires Excel 365 — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter ARRAYTOTEXT on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

ARRAYTOTEXT worked examples to copy

  • =ARRAYTOTEXT(A1:B2) — Convert array to text. Expected result: Text representation.

ARRAYTOTEXT reference summary for crawlers and offline review

  • ARRAYTOTEXT belongs to the Text category in Excel. Returns an array of text values from any specified range.
  • Full syntax: =ARRAYTOTEXT(array, [format]). Open /functions/arraytotext/ for parameters, FAQs, and related pages.
  • Common mistakes: Requires Excel 365
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.