Dynamic array formulas in Excel 365 return results that spill across multiple cells. That is powerful for analysis, but it creates a problem for display: how do you show all those values as a single readable sentence in one cell? The ARRAYTOTEXT function solves this. It converts any array or range into a single text string joined by a delimiter. The result embeds cleanly in messages, reports, and email subject lines — all from a single cell.
This guide covers the syntax, both format modes, and six practical examples. You will learn how to join names into a list, build dynamic summary sentences, combine ARRAYTOTEXT with FILTER, and format output strings for different audiences. Each example also compares ARRAYTOTEXT to TEXTJOIN, which covers many of the same use cases in older Excel versions.
What Does ARRAYTOTEXT Do?
ARRAYTOTEXT takes a range or array and concatenates all the values into a single text string. It differs from TEXTJOIN in one key way: it accepts any array expression including FILTER, UNIQUE, SORT, and SEQUENCE results. Consequently, you can pass a dynamic filtered array directly to ARRAYTOTEXT without a helper column. The result is a compact single-cell formula for complex multi-value summaries.
ARRAYTOTEXT has two format modes. Format 0 produces concise output — values separated by commas, no spaces or quotes. Format 1 produces strict output — text values are wrapped in double quotes, numbers are unquoted, and values are separated by comma-space. Format 1 is useful when the output needs to be machine-readable or when you need to distinguish text from numeric values visually.
What Is the ARRAYTOTEXT Syntax?
| Argument | Required? | What it does |
|---|---|---|
| array | Required | The range, array, or array expression to convert. Can be a cell range (A2:A10), a named range, or a dynamic array formula result (e.g. FILTER(...), UNIQUE(...), SORT(...)). Multi-row multi-column arrays are read left-to-right, top-to-bottom. |
| format | Optional | 0 (default) = concise mode: values separated by commas, no quotes around text, no spaces after commas. 1 = strict mode: text values wrapped in double quotes, numbers unquoted, comma + space separator. |
| Mode | Input array | Output string | Use for |
|---|---|---|---|
| Format 0 | {"Alice","Bob","Carol"} | Alice,Bob,Carol | Human-readable labels, email subject lines |
| Format 1 | {"Alice","Bob","Carol"} | "Alice", "Bob", "Carol" | Machine-readable output, debugging arrays |
| Format 0 | {100,200,300} | 100,200,300 | Compact numeric list |
| Format 1 | {100,200,300} | 100, 200, 300 | Spaced numeric list |
Examples 1–4: Core ARRAYTOTEXT Patterns
The simplest use is converting a range of cells into a single text string. Pass the range as the first argument and choose the format mode. Format 0 gives a compact comma-separated result. Format 1 adds spaces and quotes text values. Both modes read left-to-right, top-to-bottom — a vertical column becomes a left-to-right list.
The most powerful use is passing FILTER directly to ARRAYTOTEXT. The result is a single cell showing a comma-separated list of all matching items. This updates automatically as the data changes. No helper columns, no manual concatenation — one formula does it all. Nesting SORT inside FILTER also ensures a consistent alphabetical order.
Passing UNIQUE inside ARRAYTOTEXT produces a deduplicated list as a readable string. This is useful for generating a summary of distinct categories, tags, regions, or product lines without any duplicates. The combination updates automatically, making it ideal for dynamic report headers.
ARRAYTOTEXT always uses a comma as its delimiter — this is not configurable. However, applying SUBSTITUTE after ARRAYTOTEXT replaces the comma with any character you need. This works without switching to TEXTJOIN.
Examples 5 and 6: Applied ARRAYTOTEXT
Dynamic Summaries and Formatted Report Strings
A dynamic sentence pulls live data from your sheet into a readable narrative. As the underlying data changes, the sentence updates automatically. This is useful for email body text, automated report intros, and dashboard tooltips. Combining ARRAYTOTEXT with TEXT, COUNTA, and SUMIF builds rich sentences without any VBA.
ARRAYTOTEXT and TEXTJOIN overlap significantly in everyday use. Both join arrays into strings. The key differences are delimiter flexibility and version availability. TEXTJOIN lets you specify any delimiter and works from Excel 2019. ARRAYTOTEXT always uses a comma but accepts richer array expressions as direct input. For most everyday list-joining tasks, TEXTJOIN with a custom delimiter is more flexible. ARRAYTOTEXT shines when passing complex nested array expressions in a compact single function.
Common Issues and How to Fix Them
ARRAYTOTEXT is not available in my Excel version
ARRAYTOTEXT requires Excel 365 or Excel 2021. It is not available in Excel 2019, 2016, or any earlier version. It is also not available in Google Sheets. In those environments, use TEXTJOIN instead: =TEXTJOIN(",", TRUE, A2:A10) produces the same result as =ARRAYTOTEXT(A2:A10) for a simple range. For passing FILTER or UNIQUE results, TEXTJOIN also accepts dynamic array expressions in Excel 365 and 2021.
The output includes blank entries from empty cells
ARRAYTOTEXT does not skip blank cells automatically. Empty cells produce extra commas — for example, "Alice,,Carol" with a double comma where the blank was. The fix is to pre-filter blanks before passing to ARRAYTOTEXT. Use =ARRAYTOTEXT(FILTER(A2:A20, A2:A20<>"")) to exclude blank cells first. Alternatively, TEXTJOIN with ignore_empty = TRUE handles this automatically without a FILTER wrapper.
The output wraps or truncates in the cell
ARRAYTOTEXT returns a single text string of unlimited theoretical length. However, a cell can only display text up to 32,767 characters. In practice, display issues arise when the column is too narrow and text overflows. Wrap Text is not normally appropriate for ARRAYTOTEXT output since the result is a continuous string rather than a multi-line list. Instead, widen the column, or use the CHAR(10) separator trick from Example 4 and enable Wrap Text to display it as a vertical list.
Frequently Asked Questions
-
What does the ARRAYTOTEXT function do in Excel?ARRAYTOTEXT converts an array, range, or dynamic array expression into a single text string. It joins the values with a comma separator. Format 0 (the default) produces compact output without spaces or quotes. Format 1 (strict mode) wraps text values in double quotes, leaves numbers unquoted, and adds a space after each comma. The function is available in Excel 365 and Excel 2021. Its key advantage over manual concatenation is that it accepts complex nested array expressions — including FILTER, UNIQUE, SORT, and SEQUENCE — directly as the first argument.
-
What is the difference between ARRAYTOTEXT and TEXTJOIN?Both functions join arrays into a single text string. TEXTJOIN is more flexible: it lets you specify any delimiter, can skip blank cells automatically, and is available from Excel 2019 (including Google Sheets). ARRAYTOTEXT is more concise: it always uses a comma delimiter, does not skip blanks automatically, and offers a strict format mode that quotes text values. For custom delimiters (space, semicolon, bullet, line break), TEXTJOIN is the better choice. ARRAYTOTEXT is specifically useful when you need format 1 strict mode or when you want a compact formula for complex nested array expressions.
-
How do I use ARRAYTOTEXT with FILTER?Pass the FILTER function directly as the first argument of ARRAYTOTEXT: =ARRAYTOTEXT(FILTER(A2:A50, B2:B50="Engineering")). This returns a comma-separated list of all names in the Engineering department. The result updates automatically when the data changes. To sort the list, wrap FILTER with SORT: =ARRAYTOTEXT(SORT(FILTER(A2:A50, B2:B50="Engineering"))). To handle the case where no rows match the filter, add the third argument of FILTER: =ARRAYTOTEXT(FILTER(A2:A50, B2:B50="HR", "None")) returns "None" when HR has no employees.
More Questions About ARRAYTOTEXT
-
Can I change the delimiter in ARRAYTOTEXT?Not directly — ARRAYTOTEXT always uses a comma. However, you can wrap the result with SUBSTITUTE to replace the comma with any character: =SUBSTITUTE(ARRAYTOTEXT(A2:A10), ",", " | ") for a pipe separator, or =SUBSTITUTE(ARRAYTOTEXT(A2:A10), ",", CHAR(10)) for a line break separator in a Wrap Text cell. For maximum delimiter flexibility, switch to TEXTJOIN, which accepts any text string as the delimiter argument including spaces, bullets, semicolons, tabs, and CHAR codes.
-
What does ARRAYTOTEXT format 1 do?Format 1 (strict mode) changes two things. First, text values are wrapped in double quotes in the output string — so "Alice" appears as "Alice" with quote marks visible in the cell. Second, a space is added after each comma — so the separator becomes ", " instead of ",". Numbers are not quoted in either format. Format 1 is useful when the output will be read by another system that expects quoted string values, or when you are debugging an array and want to see which values are text vs numeric. For human-readable display, format 0 is generally cleaner.
-
Can ARRAYTOTEXT handle a 2D array (multiple rows and columns)?Yes. When you pass a multi-row, multi-column range to ARRAYTOTEXT, it reads the values left-to-right across each row, then moves to the next row. So the 2×2 range {A,B;C,D} produces "A,B,C,D" in format 0. All values from all rows and columns are flattened into a single comma-separated string. There is no row separator — the transition from one row to the next looks the same as the transition from one column to the next. If you need row separators, use TEXTJOIN with a different approach, or use ARRAYTOTEXT separately on each row and then concatenate the row results with a newline separator.