Summary
The Excel ARRAYTOTEXT function converts an array or range into a text string. Text values are passed unchanged, while non-text values are converted to text. This function is useful for generating readable text representations of array data.
Syntax
=ARRAYTOTEXT(array, [format])
- array: The array or range to convert to text. Required
- format: [Optional] The format of the returned data. It can be:
0: Default concise format, easy to read1: Strict format with escape characters and row delimiters
Return value
A text string representing the array values.
How to use
ARRAYTOTEXT is used for converting arrays to text strings, either in a concise or strict format. It’s particularly useful in data analysis, reporting, and when displaying array contents in a user-friendly text format.
Examples
Simple ARRAYTOTEXT
Converting a Simple Array to Text: Converting numeric values in a range to a text string:
=ARRAYTOTEXT(A1:A3)
If A1:A3 contains 1, 2, 3, this formula returns “1, 2, 3”.
ARRAYTOTEXT with Strict Format
Strict Format for Precise Data Representation: Using strict format to represent array data:
=ARRAYTOTEXT(A1:A3, 1)
This returns “{1;2;3}”, with semicolons separating the values, encapsulating the array structure.
ARRAYTOTEXT with Mixed Data Types
Handling Mixed Data Types in an Array: Converting a range with different data types to text:
=ARRAYTOTEXT(B1:C3, 0)
Assuming B1:C3 contains a mix of numbers and text, this formula converts them to a single text string, like “Red, 3, Green, 2, Purple, 1”.
ARRAYTOTEXT for Data Analysis
Analyzing Data with Text Output: Using ARRAYTOTEXT for data output in a readable format:
=ARRAYTOTEXT(D4:E6, 0)
Converts the range D4:E6 to a concise text format for easy analysis or reporting.
Additional Notes
- The concise format (format 0) provides a simple, comma-separated list of values.
- The strict format (format 1) includes escape characters and row delimiters, useful for reusing the output in formulas.
- ARRAYTOTEXT can handle numeric, text, Boolean, and error values within arrays.
- Useful for debugging complex formulas or creating text-based summaries of array contents.