ARRAYTOTEXT

The ARRAYTOTEXT function is used to convert an array of values into a delimited text string, which can be useful for creating human-readable summaries or reports from array data.

Syntax

=ARRAYTOTEXT(array, delimiter, [quoting], [qualifier])

array The range or array containing the values to be converted to text.
delimiter The character(s) used to separate the values in the resulting text string.
quoting (Optional) A logical value that specifies whether to enclose each value in the array with the qualifier. Defaults to FALSE if omitted.
qualifier (Optional) The character used to enclose each value if quoting is set to TRUE. Defaults to double quotation marks (").

About ARRAYTOTEXT

When you have an array of data in Excel and you want to transform it into a user-friendly text format, the ARRAYTOTEXT function comes to the rescue. It facilitates the conversion of an array of values into a delimited text string, enabling you to present the information in a clear and organized manner for reporting or informational purposes. This function is particularly handy when dealing with arrays containing diverse sets of data, such as names, numbers, or categories, and when a summarized textual representation is preferable to a tabular layout. By specifying the delimiter and, if desired, the quoting and qualifier options, you can tailor the output to suit your specific formatting needs, enhancing the readability and accessibility of the data for end-users.

Examples

Suppose you have an array of sales data containing the following values: {1000, 1500, 800, 1200, 950}. You want to convert this array into a comma-separated text string for reporting purposes. The ARRAYTOTEXT formula would be: =ARRAYTOTEXT({1000, 1500, 800, 1200, 950}, ", ") This will return the delimited text string: "1000, 1500, 800, 1200, 950".

Consider you have an array of names: {"John", "Jane", "Michael", "Emily", "David"}. You aim to create a pipe-separated text string with each name enclosed in double quotes. The ARRAYTOTEXT formula would be: =ARRAYTOTEXT({"John", "Jane", "Michael", "Emily", "David"}, "|", TRUE, "") This will produce the text string: ""John"|"Jane"|"Michael"|"Emily"|"David"".

Questions

Can the ARRAYTOTEXT function handle arrays with different data types?

Yes, the ARRAYTOTEXT function is designed to handle arrays with different data types, such as text, numeric, or mixed. It accommodates the conversion of diverse array elements into a cohesive delimited text string, ensuring a comprehensive representation of the array data.

Is it possible to use custom characters as delimiters or qualifiers with the ARRAYTOTEXT function?

Absolutely. The ARRAYTOTEXT function allows you to specify custom characters as delimiters and qualifiers, providing the freedom to tailor the formatting of the text output based on your preferences and the requirements of the target audience.

Can the ARRAYTOTEXT function handle multi-dimensional arrays?

No, the ARRAYTOTEXT function is designed to handle one-dimensional arrays or ranges. If you have multi-dimensional data, you may need to reshape or preprocess the array before using the function to ensure the desired output.

How does the quoting and qualifier options impact the output of the ARRAYTOTEXT function?

The quoting and qualifier options in the ARRAYTOTEXT function provide control over whether each value in the array is enclosed in the specified qualifier and separated by the delimiter in the resulting text string. By toggling these options, you can influence the visual presentation and clarity of the text output.

Related functions

TEXTJOIN
CONCAT
CONCATENATE
JOIN