ARRAYTOTEXT function

The ARRAYTOTEXT function in Excel is used to convert an array or range of values into a text string, with each value separated by a specified delimiter. This function is particularly useful when you need to display or output an array in a more readable or formatted way.

Syntax

=ARRAYTOTEXT(array, [delimiter], [format])

Parameters

• array (required): The array or range of cells that you want to convert into a text string.
• delimiter (optional): The text string that separates the values in the array. If not provided, Excel will use a default delimiter, such as a comma.
• format (optional): Specifies how to format the values in the array. This can be either "general", "number", or a custom format. If omitted, Excel will use the default format.

Key Points

• The ARRAYTOTEXT function helps convert a range or array into a formatted text string, which can be useful for displaying results, concatenating values, or passing data in a readable form.
• The function can also work with multi-dimensional arrays (e.g., a 2D range of cells).
• Delimiter: You can specify a delimiter to separate the values in the resulting text string, such as a comma, space, or semicolon.
• Formatting: The function allows you to specify how the array values should be formatted (e.g., general number formatting, specific date formats, etc.).

Example Usage

1. Converting a Simple Range to Text

If you have a range of values in cells A1:A3 (e.g., 10, 20, 30), you can convert these values into a text string with a comma delimiter:

=ARRAYTOTEXT(A1:A3, ", ")

This would return the following text:

10, 20, 30

2. Using a Custom Delimiter

If you want to separate the values with a space, you can change the delimiter:

=ARRAYTOTEXT(A1:A3, " ")

This would return:

10 20 30

3. Formatting the Values

If you want to format the values as numbers with two decimal places, you can use the format argument:

=ARRAYTOTEXT(A1:A3, ", ", "number")

This would return:

10.00, 20.00, 30.00

4. Converting a 2D Array to Text

If you have a 2D array (e.g., values in cells A1:B2):

You can use ARRAYTOTEXT to convert this 2D array into a string, with a comma delimiter between values:

=ARRAYTOTEXT(A1:B2, ", ")

This would return:

10, 20, 30, 40

If you prefer to format it differently, for example, separating the rows with semicolons:

=ARRAYTOTEXT(A1:B2, ", ", "general")

This would return:

10, 20; 30, 40

Notes

• Array Input: The input array can be a range, an array constant, or a result from another formula that returns an array.
• Delimiter and Format: The delimiter and format parameters are optional. If omitted, the function will use default settings (a comma separator and general format).
• Usage Scenarios: This function is useful when you need to combine or format multiple values into a single cell as text, such as for creating reports, preparing data for concatenation, or for output formatting in a readable way.

Related Functions

• TEXTJOIN: A similar function that combines values in a range or array using a specified delimiter, but with more flexibility for handling empty values.

  Example:

  =TEXTJOIN(", ", TRUE, A1:A3)
  

• CONCATENATE or CONCAT: These functions can also join text strings, but they work on a cell-by-cell basis and do not handle arrays as flexibly as ARRAYTOTEXT.

  Example:

  =CONCATENATE(A1, ", ", A2, ", ", A3)
  

• TEXT: Used to apply specific formatting to a single value, allowing you to control how individual values are displayed as text.

  Example:

  =TEXT(A1, "0.00")
  

The ARRAYTOTEXT function is great for converting arrays or ranges into formatted text strings with delimiters and specific formatting. It’s useful when you want to display array data as text for reporting or concatenation purposes.