ARRAYTOTEXT Function in Excel: Explained
In this article, you will learn how to use the ARRAYTOTEXT formula in Excel.
What is the ARRAYTOTEXT function in Excel?
When working with arrays, Excel often treats them as a series of individual cells rather than a cohesive unit. ARRAYTOTEXT overcomes this limitation by consolidating the array's elements into a single delimited text string where non-text values are converted to text values. To be delimited is to have a character separating the text values, in this case, commas and/or semicolons.
When is the ARRAYTOTEXT function helpful in Excel?
This function is particularly useful for tasks such as combining multiple values into a single cell or generating comma-separated lists. By using ARRAYTOTEXT, users can simplify data handling and analysis, improving efficiency and flexibility in Excel.
How to use the ARRAYTOTEXT function in Excel
The syntax of the ARRAYTOTEXT function is the following:
Array: The required argument array refers to the collection of cells that you want to be represented as one text string.
Format: The format is an optional argument that controls the structure of the returned text string. It can be one of two things:
- 0, This is the default setting and is the concise format. The concise format is the most readable and only has the cell contents values separated by commas (,).
- 1, This is the strict format. The text values will be in double quotation marks (“”), the rows will be separated by semicolons (;), and the columns will be separated by commas (,).
To input the format into the function simply type 0 or 1.
Note of Caution: If you input anything other than 0 or 1 as the format argument, the ARRAYTOTEXT function will return a #VALUE! Error.
Suppose you're managing a construction project and have a list of materials in one column of an Excel sheet, and their quantities required in another column. Now you want to create a new single list that combines both pieces of information for easy reference. Below is a screenshot of how you could use ARRAYTOTEXT in Excel to do this.
With this, you now have an easily readable list of the materials and their quantities required for the construction project.
What is the difference between the ARRAYTOTEXT, CONCATENATE, and TEXTJOIN functions in Excel?
The ARRAYTOTEXT, CONCATENATE, and TEXTJOIN functions are similar in that they work to combine information from different cells, however, they differ in their arguments and what situations they are most useful in.
- The ARRAYTOTEXT is used to convert an array into a singular text string with either a concise or strict format. It does not have the ability to ignore empty cells.
- The CONCATENATE function is used to join two or more text strings into one text string. You have to select each cell one by one that you want to concatenate. It does not have the ability to use a delimiter nor does it ignore empty cells.
- The TEXTJOIN function allows you to specify a delimiter that will be inserted between each text item to be combined. It also allows you to ignore or include empty cells in the range of cells to be joined.
It is up to the user to see what function is most beneficial for the particular circumstances.
Go to the page LiveFlow‘s How to Guides to find more information about Excel and Google Sheets formulas and tips that were not covered here.
