The ARRAYTOTEXT function takes two arguments: array and format. Array is the array or range to convert to text. Array can be provided as a range like A1:A3 or an array like {1,2,3}. The format argument controls the structure of the output. By default, format is zero and ARRAYTOTEXT will output a “concise” format that displays plain unquoted values and no curly braces. Essentially, concise format is a plain, human-readable format. When format is set to 1 (strict format), text values will be enclosed in double quotes ("") and the delimiter between items will follow the structure of the array provided, with semicolons (;) separating rows and commas (,) separating columns. When format is 1, the output from ARRAYTOTEXT can be used directly in the Excel formula bar.
Examples
With the values 1, 2, and 3 in cells A1:A3: Notice both results are text values, but in the second example values are separated by semicolons and the output is enclosed in curly braces. The strict format option will also wrap text values in double quotes (""). For example, with “red”, “blue”, and “green” in A1:A3: In the example shown above, the formula in E11 refers to a range that contains both text values and numbers: Notice only the text values in the second formula are enclosed in double quotes. In addition, rows are separated by semicolons and columns are separated by commas, following the structure of arrays in Excel.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
