The `ARRAYTOTEXT` function in Excel is available in Microsoft 365 and Excel 2021 as part of the newer Excel functions designed to work with dynamic arrays. This function converts an array or a range of values into a single text string, and it can be especially useful when you want to present data in a particular textual format. Here’s how you can use it:
Syntax
ARRAYTOTEXT(array, [format])
- array: This is required. It is the range or array you want to convert into text.
- format: This is optional. It determines the format of the output:
- If `format` is 0 or omitted, the function uses a compact form where it displays text without quotes and numeric values in standard format.
- If `format` is 1, the function uses a more readable form where text is shown with quotes, uses semicolons as separators for array elements, and numbers are formatted based on your locale settings.
Examples
- Basic Usage
Suppose you have data in cells A1:A3:
A1: Apple
A2: Banana
A3: Cherry
You can use `ARRAYTOTEXT` to combine these into a single text string:
=ARRAYTOTEXT(A1:A3)
This would output:
Apple,Banana,Cherry
- Using the Format Argument
If you want the text elements to include quotes and for the output to be more explicit:
=ARRAYTOTEXT(A1:A3, 1)
This would output:
"Apple";"Banana";"Cherry"
Tips
- Dynamic Arrays: Since the `ARRAYTOTEXT` function works well with dynamic arrays, ensure your Excel supports these features.
- Locale Settings: When using the function with `format` set to 1, the representation can vary depending on your locale settings, affecting things like number formats and delimiters.
- Data Types: `ARRAYTOTEXT` is designed to handle various data types within the array, including text, numbers, and even dates (which will be represented in their native formats).
If you have a spreadsheet with dynamic data and wish to integrate it into other applications or formats, `ARRAYTOTEXT` provides a straightforward way to convert your array outputs into strings.
Unlock Your Potential
Excel
Access
Power BI
