Ravindra BagaleCourses & study guides Track your progress

Excel ARRAYTOTEXT Function

By · Last updated:

Chala mitrano — today we learn ARRAYTOTEXT. Read Why → What → How, then try the practice list.

Version note. Microsoft 365.
Fictional sample data for learning. Tiny demo arrays only.

Purpose

Convert an array to text (for display, encoding, or exporting a readable snapshot).

Return value

Text representation of the array (concise or strict formats per Microsoft docs).

Syntax

ARRAYTOTEXT(array, [format])

Arguments

ArgumentRequired?Description
arrayRequiredArray or range to convert.
formatOptional0 concise (default) or 1 strict — confirm on docs.

Version

Microsoft 365.

How to use — Why → What → How

Why: Sometimes you must show a spill as a single text cell for notes, logs, or debugging.

What: ARRAYTOTEXT flattens the array into a text string following Excel’s rules.

How — numbered steps:

  1. Try =ARRAYTOTEXT(A1:C3).
  2. Compare format 0 vs 1 on the same grid.
  3. Use for audit notes, not as a replacement for proper CSV export when you need files.
  4. Prefer TEXTJOIN when you only need a simple delimiter list.
  5. Watch length limits on huge arrays.

Ravindra Bagale's Tip

For human lists, TEXTJOIN is often clearer. ARRAYTOTEXT follows Excel’s array-text rules.

Ravindra Bagale's Tip

Do not put secrets into ARRAYTOTEXT cells you will share.

Worked examples

1) Small grid to text

A1:B2.

Formula: =ARRAYTOTEXT(A1:B2)

Result: Text snapshot

What it means: Readable array text.

2) Strict format

Compare.

Formula: =ARRAYTOTEXT(A1:B2,1)

Result: Strict text

What it means: Format argument.

3) After UNIQUE

Cities.

Formula: =ARRAYTOTEXT(UNIQUE(A2:A50))

Result: Unique cities as text

What it means: Compose.

4) Debug MAP output

See MAP result in one cell.

Formula: =ARRAYTOTEXT(MAP(B2:B5, LAMBDA(x,x*2)))

Result: Text of mapped values

What it means: Debugging aid.

5) vs TEXTJOIN

Comma list.

Formula: =TEXTJOIN(", ", TRUE, A2:A10)

Result: a, b, c…

What it means: Often nicer for people.

Common errors

#VALUE!: Bad format argument.
Length issues: Huge arrays make giant strings.

Practice tasks (10+)

  1. Task 1. ARRAYTOTEXT on a 3×3 block.
    Hint: try on a tiny range first
  2. Task 2. Try format 0 and 1.
    Hint: try on a tiny range first
  3. Task 3. ARRAYTOTEXT(UNIQUE(...)).
    Hint: try on a tiny range first
  4. Task 4. Compare to TEXTJOIN.
    Hint: try on a tiny range first
  5. Task 5. Use as a debug line under MAP.
    Hint: try on a tiny range first
  6. Task 6. Explain when not to use it for exports.
    Hint: try on a tiny range first
  7. Task 7. Interview contrast with TEXTJOIN.
    Hint: try on a tiny range first
  8. Task 8. Keep secrets out of shared text dumps.
    Hint: try on a tiny range first
  9. Task 9. LET + ARRAYTOTEXT for a labelled note.
    Hint: try on a tiny range first
  10. Task 10. Bonus: length of the resulting text with LEN.
    Hint: try on a tiny range first

Quick quiz

Q1. ARRAYTOTEXT turns arrays into text.
Q2. TEXTJOIN is sometimes clearer for simple lists.
Q3. Dumping secrets into text cells is fine.
Q4. Needs a modern Excel that has the function.

Answers: Q1=True, Q2=True, Q3=False, Q4=True

Related: LAMBDA · LET · GROUPBY · Function hub · Learning path