Excel ArrayToText Function

What is ArrayToText function in Excel?

The ArrayToText function is one of the Text functions of Excel.

It Returns a text representation of an array.

We can find this function in Text of insert function Tab.

How to use ArrayToText function in excel

1. Click on an empty cell (like F5).

2. Click on the fx icon (or press shift+F3).

3. In the insert function tab you will see all functions.

4. Select Text category.

5. Select ArrayToText function

6. Then select ok.

7. In the function arguments Tab you will see ArrayToText function.

8. Array section is the array to represent as text.

9. Format section is the format of the text.

10. You will see the results in the formula result section.

Examples of ArrayToText function in Excel

What is the purpose of ArrayToText function?

ArrayToText function returns a text representation of an array.

Values are separated by either commas (,) or semicolons (;).

=ARRAYTOTEXT({1;2;3;4;5},0)  ---->>>> returns "1,2,3,4,5"

How many arguments does ArrayToText function have?

The ARRAYTOTEXT function takes two arguments: array and format. 

1. Array is the array to represent as text.

2. Format is the format of the text.

What is Array argument in ArrayToText function?

Array is range of text or numbers. 

Array can be provided as a range like B3:C5 or an array like {1,”book”;2,”image”;3,”voice”} or {1;2;3;4;5}.

What is Format argument in ArrayToText function ?

Format is the format of the text and By default, format is zero (concise format) with unquoted values and no curly braces.

=ARRAYTOTEXT({1;2;3;4;5})  ---->>>> returns "1,2,3,4,5"

=ARRAYTOTEXT({1;2;3;4;5},0)  ---->>>> returns "1,2,3,4,5"

When format is set to 1 (strict format), text values will be enclosed in double quotes (“”).

=ARRAYTOTEXT({1;2;3;4;5},1)  ---->>>> returns "{1;2;3;4;5}"

When format is 1, the output from ARRAYTOTEXT can be used directly in the Excel formula bar.

What is the difference between colon and semicolon in ArrayToText function?

Every colon shows that values are in a row and each semicolon shows that values are in a column.

=ARRAYTOTEXT(B3:C5,1)  ---->>>> returns {1,"book";2,"image";3,"voice"}

HOW to find unique text values in excel

By combining the following two functions (ARRAYTOTEXT and UNIQUE), you can find the unique values ​​of each database.

=ARRAYTOTEXT(UNIQUE(category)) ---->>>> returns book, image, voice

ArrayToText related functions

• VALUETOTEXT function
• TEXT function