How to Use ARRAYTOTEXT Function in Excel 365?

—

by

Himanshu
in

✅ What is the ARRAYTOTEXT Function?

ARRAYTOTEXT(array, [format]) is a text function in Excel 365 that converts an array (range of values or formulas) into a single text string.
It is helpful when you want to visualize or export the values of a range as a single readable string.

Syntax

ARRAYTOTEXT(array, [format])
  • array – Required. The array or range to convert.
  • format – Optional.
    • 0 (default): Returns compact format (best for ranges with just values).
    • 1: Returns strict format (adds quotes and brackets, good for formulas or nested arrays).

Priya runs a home tiffin service in Pune. Every day, she notes down the dishes prepared in Excel. Her customers often ask for the daily menu via WhatsApp.

She wants a quick way to convert the dish list in cells (A2:A6) into a single line of text that she can copy and send.

Data in Excel

A2: Roti
A3: Paneer Butter Masala
A4: Dal Tadka
A5: Rice
A6: Salad

Use ARRAYTOTEXT

=ARRAYTOTEXT(A2:A6)

Output (Compact format):

Roti,Paneer Butter Masala,Dal Tadka,Rice,Salad

✅ Now Priya can copy-paste this line into WhatsApp quickly.

🛠 Strict Format Option

If Priya uses:

=ARRAYTOTEXT(A2:A6, 1)

She gets:

{"Roti";"Paneer Butter Masala";"Dal Tadka";"Rice";"Salad"}

Useful if she needs to paste it into a formula or some system that requires strict array syntax.

🎯 When to Use ARRAYTOTEXT?

  • Sending list of items via email/WhatsApp (like Priya).
  • Logging or displaying a formula output for review.
  • Saving snapshot of values in formulas (for audit or export).
  • Creating readable logs from dynamic data.

✅ Summary

FeatureDetails
Function NameARRAYTOTEXT
PurposeConverts a range/array to a single text
Optional ArgumentFormat: 0 (compact), 1 (strict)
Real-life Use CaseTiffin service menu sharing, student name list export, audit logs

On sale products