How to Use the VALUETOTEXT and ARRAYTOTEXT Functions in Microsoft Excel

Preparing data for reporting, API payloads, or dynamic spreadsheet summaries frequently requires converting numeric values, dates, or multi-cell ranges into clean, delimited text strings. Historically, Excel users relied on nested TEXTJOIN, CONCATENATE, or custom VBA routines to flatten arrays into text. Microsoft Excel includes two streamlined text-conversion functions: VALUETOTEXT and ARRAYTOTEXT. These native functions allow users to convert individual values and multi-dimensional cell arrays into formatted strings or strict formula-syntax representations instantly.

Understanding VALUETOTEXT vs ARRAYTOTEXT

  • VALUETOTEXT: Converts a single value (or spilled dynamic array cell) into text. It provides options to return either the visually formatted text or an exact formula representation containing quotes and escape characters.
  • ARRAYTOTEXT: Flattens an entire multi-row or multi-column range into a single comma-delimited text string, with optional support for strict array syntax (braces and semicolons).

Function Syntaxes and Formatting Modes

VALUETOTEXT Syntax

=VALUETOTEXT(value, [format])
  • value: The cell, number, date, boolean, or error value to be transformed.
  • format: An optional argument that accepts:
    • 0 (Concise mode – default): Returns the text as displayed without quotation marks.
    • 1 (Strict mode): Returns the literal string representation wrapped in quotation marks, preserving escape characters suitable for formulas.

ARRAYTOTEXT Syntax

=ARRAYTOTEXT(array, [format])
  • array: The continuous cell range, table column, or dynamic spill array (e.g., A2:C5 or A2#).
  • format: An optional argument that accepts:
    • 0 (Concise mode – default): Joins all items separated by commas and spaces (e.g., Apple, Banana, Orange).
    • 1 (Strict mode): Outputs array constant syntax enclosed in curly braces with semicolons denoting row breaks (e.g., {"Apple", 10; "Banana", 20}).

Practical Example 1: Creating a Delimited Summary List with ARRAYTOTEXT

Suppose you have a list of approved vendors in cells A2:A8 that you need to embed into an executive email summary paragraph:

="Approved suppliers for Q3 are: " & ARRAYTOTEXT(A2:A8)

If cells A2:A8 contain Alpha, Beta, Gamma, Delta, the formula outputs:

Approved suppliers for Q3 are: Alpha, Beta, Gamma, Delta

Unlike TEXTJOIN, you do not need to specify delimiter parameters or configure empty cell handling arguments; ARRAYTOTEXT handles array flattening automatically.

Practical Example 2: Generating Formula Constants with Strict Mode

When constructing automated documentation, testing formulas, or preparing parameters for LAMBDA expressions, strict mode (format 1) renders arrays into valid Excel syntax:

=ARRAYTOTEXT(A2:B4, 1)

If range A2:B4 contains two columns of names and numbers, the resulting single cell contains:

{"North", 1500; "South", 2300; "East", 1850}

This exact text string can be copied and pasted directly into any Excel formula as a hard-coded array constant.

Practical Example 3: Sanitising Data Types with VALUETOTEXT

When feeding user input into downstream lookup functions such as XLOOKUP or string comparisons where numbers might accidentally be stored as numeric while lookup tables expect text strings:

=XLOOKUP(VALUETOTEXT(D2), A2:A50, B2:B50)

VALUETOTEXT(D2) ensures that regardless of whether cell D2 holds an integer, date, or text string, it is cast as pure text without altering the source cell formatting.

Key Differences Between ARRAYTOTEXT and TEXTJOIN

  • Simplicity: ARRAYTOTEXT requires only a single argument to produce clean comma-separated lists, whereas TEXTJOIN requires three arguments (delimiter, ignore_empty, range).
  • Multi-Dimensional Handling: TEXTJOIN flattens 2D tables into a continuous list without structural indicators, whereas ARRAYTOTEXT(range, 1) preserves row and column structures using standard Excel array delimiters (commas for columns, semicolons for rows).
  • Custom Delimiters: If your workflow requires semicolons, pipe symbols (|), or line breaks as custom delimiters, continue using TEXTJOIN.

Get the best tech tips delivered straight to your inbox.

Join thousands of readers mastering Apple, Google, Microsoft, and Linux.