Excel ARRAYTOTEXT function

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 read
    • 1: 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.

Related Functions

Excel TEXT function

The Excel TEXT function formats numbers as text using specified formats, crucial for displaying numbers in readable formats.

Excel VALUETOTEXT function

The Excel VALUETEXT function converts a cell's value to a text format, preserving number formatting, essential for dynamic text displays.

Content Navigation