Pexels
Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web
The ARRAYTOTEXT Function Syntax
The ARRAYTOTEXT function needs to be inputted into a cell like a formula for it to work properly. To add this function to a cell the following steps need to be taken: Below in bold is the syntax of the ARRAYTOTEXT function. In addition, there is an explanation for each argument. =ARRAYTOTEXT(Array, [Format]) Array - The array or the range of cells that are to be returned as text. Format - (Optional) This is the format option of the returned text and can only be a 0 or 1. If nothing is added the 0 will default.
0 - Text will look the same as values in rendered cells. 1 - A string is generated that can be parsed in a formula. Escape characters of the text are shown.
Example 1: Array With Null Values
An array of data or even a single value can be used with this function. When using an array of data, you may encounter empty cells. See the example below that shows the results of converting the array data into text. The format 0 result shows that if an array is used that spaces are not neglected. As you can, empty cells are represented by a space followed by a comma. In the format 1 option, the spaces are removed with the escape characters present.
Example 2: Complete Array
The second example below shows a complete array without any spaces. The results have a more standardized appearance which will not always be the case.
Example 3: Using a Defined Range
A defined range can also be used in this formula. To define a range, select the range and enter a name into the name box near the formula bar. The defined range should appear as an option when you start typing the range’s name in the place of the array.
Inserting the ARRAYTOTEXT Function
The ARRAYTOTEXT function can be inserted into a cell by selecting the cell, then clicking on the formulas tab. Next, click on the text button and select ARRAYTOTEXTV from the list of functions.
Select the Function
Once the formula builder appears, the arguments can be added to see the results at the bottom of this sidebar. After the arguments are added, the done button can be clicked for the function to be entered into the cell.
Enter Arguments in the Formula Builder
This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional. © 2022 Joshua Crowder
