How to use the ARRAYTOTEXT function
What is the ARRAYTOTEXT function?
The ARRAYTOTEXT function concatenates values from a given cell range or array using the comma and/or semicolon as delimiters, the result is a text string.
What is an array?
An array in Excel is a group of values, it begins with a curly bracket and ends with a curly bracket. The values are separated by a specific delimiting character for rows and another delimiting character for columns.
Here is an example of an array: {"Car", "Bike";"Train",5} You can enter this array in a cell but remember to use an equal sign first like this: {"Car", "Bike";"Train",5}
Excel 365 spills the values to adjacent cells automatically as far as needed, however, older Excel versions require you to enter the formula as an array formula to see the values in a cell each.
The ARRAYTOTEXT function lets you use both an array and cell range, however, not both at the same time. An example of an array in the ARRAYTOTEXT function is: ARRAYTOTEXT({"Car", "Bike";"Train",5}, 0)
What is a cell range?
A cell range is basically multiple adjacent cells in worksheet. You reference a cell range using the column and row values like this: =A3:B5 so the ARRAYTOTEXT function becomes ARRAYTOTEXT(A3:B5, 0)
What is a delimiter?
It is basically a string that separates the output values from the ARRAYTOTEXT function. The ARRAYTOTEXT function uses a comma as a delimiter if the second argument is 0 (zero) and both comma and semicolon if the second argument is 1.
What is concatenate?
This means that Excel links the values in a series forming a text string.
What is a text string?
A text string in Excel is a group of characters that are used as data in a worksheet. Text strings are often a word or multiple words but may also include letters, numbers, special characters, the dash symbol, or the number sign. Text strings are left-aligned in a cell while numbers are right-aligned, and boolean values are centered.
Here are some examples of text strings in Excel:
"Hello!"
"555-0123456789"
Note that, in a formula, you need to begin a text string with a double quote and end it with another double quote. However the output from a formula shows no double quotes.
Table of Contents
1. ARRAYTOTEXT Function Syntax
ARRAYTOTEXT(array, [format])
2. ARRAYTOTEXT Function Arguments
| array | A cell reference or an array. |
| [format] | Optional. This argument lets you change the output format.
0Â - Default. Values have a delimiting comma. 1 - Strict format. Both column and row delimiters are used. Encloses all values with quotes except for errors, numbers, and boolean values. |
3. ARRAYTOTEXT Function example - concise form
This example shows the ARRAYTOTEXT function with a 0 (zero) as the second argument.
Formula in cell C3:
It returns a text string containing values from cell range B3:B14 with a comma and a space character as delimiting characters. Blank empty cells are also included.
4. ARRAYTOTEXT Function example - strict form
This example demonstrates how the ARRAYTOTEXT function returns values in strict form.
Formula in cell C3:
Here is what differ compared to concise form:
- A semicolon and a space character are used as delimiting characters between rows.
- Encapsulates the result with curly brackets.
- Text values begin and end with a double quote.
and this is what is shared by both the concise and strict forms:
- A comma and a space character are used as a delimiting value between columns.
5. ARRAYTOTEXT Function not working
The ARRAYTOTEXT function concatenates error values in to the result, this is demonstrated in cell D3, it contains both #N/A error and #DIV/0! error.
A #VALUE! is returned if [format] argument is not equal to 0 (zero) or 1.
6. ARRAYTOTEXT Function - compare related functions and techniques
The ARRAYTOTEXT Function is not the only function that joins values in Excel, there are more functions and a operator to choose from as well.
- The ampersand character & lets you concatenate values in a Excel formula. Ampersand
- The CONCATENATE function is a simple function that allows you to quickly join values. The downside is that you need to select each cell one by one. Concatenate
- The CONCAT function is a simple function that allows you to quickly join values from a cell range, however, you can't specify a delimiting value. Concat
- The TEXTJOIN function is more advanced, it lets you specify a delimiting value and ignore blank values. It takes multiple non adjacent cell ranges. TEXTJOIN
- Function key F9 lets you convert the formula to the output result. Select the cell containing the formula you want to convert. Press with left mouse button on in the formula bar so the prompt appears. Select the entire formula. Press F9 on your keyboard. The output is shown. Press Esc key to go back to the original formula or press Enter to keep the changes.
Useful resources
ARRAYTOTEXT function - Microsoft support
Functions in 'Text' category
The ARRAYTOTEXT Function function is one of 30 functions in the 'Text' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form