How to use ARRAYTOTEXT Formula in Excel?

ARRAYTOTEXT Formula in Excel is used to convert an array of text and non-text values into text. The ARRAYTOTEXT formula in Excel is a part of text formulas.

The article in this webpage describes how to use ARRAYTOTEXT formula in Microsoft Excel with syntax, detailed explanation and examples.

Subscribe Rath Point's official YouTube channel to know the latest updates, tips, and tricks.

What is ARRAYTOTEXT Formula in Excel?

The ARRAYTOTEXT formula in Excel returns an array or range to text format. The ARRAYTOTEXT formula converts the non-text values to text without changing the text values in an array. In other words, the Excel ARRAYTOTEXT formula converts any array of values to a single text string.

What is the Syntax of ARRAYTOTEXT Formula in Excel?

The Syntax of Excel ARRAYTOTEXT formula is:

=ARRAYTOTEXT(array, [format])

What are the Arguments of ARRAYTOTEXT Formula in Excel?

The Arguments of ARRAYTOTEXT formula in Microsoft Excel are explained below in detail and summary for your easy reference:

Array (Required Argument)

This is the array or range to convert as text.

Format (Optional Argument)

This is the format of the text. It can be two values:

• 0: Concise format (default). It is the same format as the default text in a cell. It separates the text with comma (,).
• 1: Strict format. It contains row delimiters and escape characters. It separates the text with curly braces for numbers and double quotes for text.

Summary of ARRAYTOTEXT Arguments:

• Array (Required) – cell range.
• Format (Optional) – concise (0) and strict (1).

How to use ARRAYTOTEXT Formula in Excel with Examples?

Example 1 (Concise Format)

In ARRAYTOTEXT formula, the default format is concise format. Its easy to read and the texts are separated by comma (,). The results of ARRAYTOTEXT formula will be same if you can type zero (0) or ignore this argument. The below image shows multiple examples of ARRAYTOTEXT formula with concise format:

Example 2 (Strict Format)

In ARRAYTOTEXT formula, the strict format separates the text with curly braces for numbers and double quotes for text. The below image shows multiple examples of ARRAYTOTEXT formula with strict format:

What are the Key Points for ARRAYTOTEXT Formula in Excel?

You must remeber the below key points while using the Excel ARRAYTOTEXT formula:

• The ARRAYTOTEXT formula returns as #VALUE error if the format is other than 0 or 1.
• The array should be in a range instead of cells separated by comma.
• In ARRAYTOTEXT formula, the strict format separates the numbers by brackets instead of comma.