Table of Contents
Converting an array to text in Excel is a simple process that allows you to display the values of an array in a single cell as a text string. This can be useful when you want to combine multiple cell values into one cohesive text string. To convert an array to text, you can use the CONCATENATE function or the ampersand (&) operator to combine the values, separated by a delimiter if desired. This will result in a single text string that can be easily copied and pasted or used in other formulas. By converting arrays to text, you can efficiently manage and manipulate data in your Excel spreadsheets.
The ARRAYTOTEXT function returns an array of text values from any specified range. It passes text values unchanged, and converts non-text values to text.
Syntax
ARRAYTOTEXT(array, [format])
The ARRAYTOTEXT function syntax has the following arguments.
Argument | Description |
|---|---|
array | The array to return as text. Required. |
format | The format of the returned data. Optional. It can be one of two values: 0 Default. Concise format that is easy to read. The text returned will be the same as the text rendered in a cell that has general formatting applied. 1 Strict format that includes escape characters and row delimiters. Generates a string that can be parsed when entered into the formula bar. Encapsulates returned strings in quotes except for Booleans, Numbers and Errors. |
Notes:
The concise format returns a list of values inside one cell, whereas the strict format returns an array of the same size and shape as the input.
If format is anything other than 0 or 1, ARRAYTOTEXT returns the #VALUE! error value.
Examples
Copy the example data in the following table and paste it in cell A1 of a new Excel worksheet. If you need to, you can adjust the column widths to see all the data.
Note To convert “Seattle” to a linked data type, Select cell B3, and then select Data > Geography.
Data | |
TRUE | #VALUE! |
1234.01234 | Seattle |
Hello | $1,123.00 |
Formula | Result |
=ARRAYTOTEXT(A2:B4,0) | TRUE, #VALUE!, 1234.01234, Seattle, Hello, 1,123 |
=ARRAYTOTEXT(A2:B4,1) | {TRUE,#VALUE!;1234.01234,”Seattle”;”Hello”,”1,123″} |
Need more help?
You can always ask an expert in the Excel Tech Community or get support in Communities.
Cite this article
stats writer (2024). How can I convert an array to text in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-convert-an-array-to-text-in-excel/
stats writer. "How can I convert an array to text in Excel?." PSYCHOLOGICAL SCALES, 28 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-convert-an-array-to-text-in-excel/.
stats writer. "How can I convert an array to text in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-convert-an-array-to-text-in-excel/.
stats writer (2024) 'How can I convert an array to text in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-convert-an-array-to-text-in-excel/.
[1] stats writer, "How can I convert an array to text in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I convert an array to text in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.