Skip to main content

How to Use the ARRAYTOTEXT Function in Excel for Mac

Joshua earned an MBA from USF and he writes mostly about software and technology.

The ARRAYTOTEXT function gives an Excel user the ability to return the text representation of an array of data.

The ARRAYTOTEXT function gives an Excel user the ability to return the text representation of an array of data.

The Purpose of the ARRAYTOTEXT Function

The ARRAYTOTEXT function gives an Excel user the ability to return the text representation of an array of data. Currently, this function is only available for use in the following formats:

  • Excel for Microsoft 365
  • Excel for Microsoft 365 for Mac
  • Excel for the web
The ARRAYTOTEXT function is being edited here with help tips appearing.

The ARRAYTOTEXT function is being edited here with help tips appearing.

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:

  1. A cell needs clicked type "=ARRAYTOTEXT(".
  2. After the open parenthesis, the array needs to be added followed by a comma.
  3. Next, include the format followed by a closing parenthesis.
  4. After the formula is created the formula can be entered.

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.

Scroll to Continue
how-to-use-the-arraytotext-function-in-excel-for-mac

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.

how-to-use-the-arraytotext-function-in-excel-for-mac

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.

how-to-use-the-arraytotext-function-in-excel-for-mac

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 from the list of functions.

Select the Function

The ARRAYTOTEXT function is selected from a list of functions.

The ARRAYTOTEXT function is selected from a list of functions.

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

The formula builder for the ARRAYTOTEXT function guides you to use the function appropriately while also displaying what the function will return.

The formula builder for the ARRAYTOTEXT function guides you to use the function appropriately while also displaying what the function will return.

References

Microsoft. (n.d.). ARRAYTOTEXT function. Retrieved July 27, 2022, from https://support.microsoft.com/en-us/office/arraytotext-function-9cdcad46-2fa5-4c6b-ac92-14e7bc862b8b

To learn more about using functions in Excel I recommend The Excel 2019 Bible book. I have been using the Excel Bible for years to improve my understanding of all aspects of this Microsoft product.

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

Related Articles