Overview of the Excel ARRAYTOTEXT Function
The Excel ARRAYTOTEXT function is designed to convert an array or range into a text string, which can be helpful for formatting and data transformation. It allows you to view array data as text and control the formatting based on specific requirements. For more details, refer to Microsoft’s official ARRAYTOTEXT function documentation.
Syntax
=ARRAYTOTEXT(array, [format])
Arguments
- array: (Required) The array or range of cells you want to convert to text.
- format: (Optional) The format for the output text. Use 0 for compact formatting (no brackets or separators) or 1 for structured formatting (brackets and comma separators). Default is 1 if omitted.
Version
Introduced in Excel 2021 and available in Excel for Microsoft 365.
Purpose
The Excel ARRAYTOTEXT function converts an array or range to a text string, either in compact or structured format. This function is particularly useful when working with dynamic arrays or preparing data for export or display as plain text.
=ARRAYTOTEXT(array, [format])
Examples of the Excel ARRAYTOTEXT Function
Example 1: ARRAYTOTEXT Function with Compact Formatting
Let’s convert a range (B4:D6) to text in compact format, using 0 as our [format]:
The formula we have in cell B8 is:
=ARRAYTOTEXT(B4:D6, 0)
Explanation:
- Converts the values in cells B4:D6 into a single text string without brackets or separators.
- Since we had numbers 1 through 9 in cells B4:D6, we get 1 through 9 in cell B8, separated by commas.
Example 2: ARRAYTOTEXT Function with Structured Formatting
Now, suppose you want to convert a range (B4:D6) to text in structured format, using 1 as our [format]:
The formula we have in cell B8 is:
=ARRAYTOTEXT(B4:D6, 1)
Explanation:
- Converts the values in cells B4:D6 into a single text string with brackets and semi-colons as separators.
- Since we had numbers 1 through 9 in cells B4:D6, we get 1 through 9 in cell B8, separated by commas, semi-colons (new rows) and brackets.
Example 3: Nested ARRAYTOTEXT Function with Multiple Rows and Columns
Let’s combine the ARRAYTOTEXT function with TEXTJOIN to convert a multi-row array (B4:D5) into a single text string with rows separated by semicolons:
The formula we have in cell B7 is:
=TEXTJOIN(“; “, TRUE, ARRAYTOTEXT(B4:D4, 0), ARRAYTOTEXT(B5:D5, 0))
Explanation:
- Converts B4:D4 and B5:D5 to text and joins them with a semicolon separator via the TEXTJOIN function.
- Use of the TEXTJOIN function alongside the ARRAYTOTEXT function allows us to combine multiple strings and adjust the output. In this case, we have a similar output as if we had used 1 as the [format], but our result is without the brackets at the beginning and end.
Notes
- Compact vs. Structured Formatting: Use 0 for a simpler text output and 1 for a structured format with separators and brackets.
- Compatibility: ARRAYTOTEXT works well with functions like TEXTJOIN and CONCAT for advanced text processing.
- Official Documentation: For more examples and guidance, see Microsoft’s ARRAYTOTEXT function documentation.
Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has been consulting on and teaching best practices in Microsoft Excel to over 400 companies across 30+ countries.
Want to learn more?
Join My Excel Academy Today!
This All-Access Pass Includes:
- Access to All My Excel Academy Courses
- 20+ hours of on-demand video
- 1-on-1 instructor support
- Mobile learning supported
- Frequently updated content
- Learn at your own pace
- 500+ practice problems
- Certificate of completion
30-Day Money-Back Guarantee
Want to learn more?
Join My Excel Academy Today!
All-Access Pass Includes:
- Access to All My Excel Academy Courses
- 20+ hours of on-demand video
- 1-on-1 instructor support
- Mobile learning supported
- Frequently updated content
- Learn at your own pace
- 500+ practice problems
- Certificate of completion
30-Day Money-Back Guarantee
Want to learn more?
Join My Excel Academy Today!
30-Day Money-Back Guarantee
This all-access pass includes:
- Access to all My Excel Academy Courses
- 20+ hours of on-demand video
- 1-on-1 instructor support
- Mobile learning supported
- Frequently updated content
- Learn at your own pace
- 500+ practice problems
- Certificate of completion
Don’t hesitate – join now!
