What is the difference between VALUETOTEXT and the TEXT function?

VALUETOTEXT (Excel 365 only) converts any value type using Excel's built-in format codes and is newer and more flexible. TEXT is older, broader-compatible, and optimized for dates and numbers. For basic date formatting, TEXT is still often simpler; for modern sheets needing precise formatting of mixed types, VALUETOTEXT is preferred.

Can VALUETOTEXT convert an array or range to text?

No. VALUETOTEXT converts a single value only. If you need to convert an entire array of values to text at once, use ARRAYTOTEXT instead, which is specifically designed for ranges.

What happens if I omit the format parameter?

VALUETOTEXT uses General format (code 0) by default, converting the value to its simplest text representation without special formatting. For example, 5.5 returns "5.5" and dates return their serial number or default text. Include a format code only if you need specific formatting like decimals or date layout.

Why does my VALUETOTEXT formula return #VALUE! when I use a custom format code?

VALUETOTEXT does not accept custom format strings like "mm/dd/yyyy"; it only recognizes predefined Excel format codes (0–49). If you need a custom format, use the TEXT function instead, which accepts custom format strings like =TEXT(E2, "mm/dd/yyyy").

VALUETOTEXT function

Converts a value to its formatted text representation, supporting numbers, dates, and other value types with customizable format codes.

=VALUETOTEXT(value, [format])

Generate a VALUETOTEXT formula

Describe what you need. The generator will reach for VALUETOTEXT where VALUETOTEXT is the right tool, and tell you when it is not.

Arguments

How VALUETOTEXT reads its arguments

Argument	Required	Description
value	Required	The value to convert to text. Can be a number, date, boolean, or cell reference. Required; if omitted, returns #VALUE! error.
format	Optional	Optional format code (typically 0–49) controlling how the value displays: 0 = General, 2 = two decimals, 14 = date M/D/YYYY, etc. Omitting format defaults to General format. Invalid or out-of-range codes return #NUM! or #VALUE!.

Returns

A text string containing the formatted representation of the input value.

Availability

Excel: 365 · Google Sheets: Not available

Worked examples

1. Format inventory quantity to precise decimals

Ingredient	Supplier	Unit	Qty	Expiry
Olive Oil	Imports Co	L	5.5	2026-12-15

=VALUETOTEXT(D2, 2)

Result: 5.50

Converts the numeric quantity 5.5 to text string "5.50" with exactly 2 decimal places. Useful for inventory exports where quantity formatting must be consistent—fractions like 5.5 liters always display with two decimals, ensuring spreadsheets and labels align.

2. Display expiry dates as formatted text

Ingredient	Supplier	Unit	Qty	Expiry
Tomatoes	Local Farm	kg	12	2026-09-20

=VALUETOTEXT(E2, 14)

Result: 09/20/2026

Converts the date 2026-09-20 to formatted text "09/20/2026" using format code 14 (M/D/YYYY). This ensures expiry dates print consistently on inventory tags and export reports, avoiding ambiguous date formats that might confuse staff.

3. Build readable inventory labels combining fields

Ingredient	Supplier	Unit	Qty	Expiry
Mozzarella	Dairy Plus	kg	3.2	2026-09-28

=VALUETOTEXT(A2)&" ("&VALUETOTEXT(D2)&" "&C2&")"

Result: Mozzarella (3.2 kg)

Concatenates ingredient name, formatted quantity, and unit into a single text label "Mozzarella (3.2 kg)". VALUETOTEXT ensures the quantity 3.2 converts reliably to text before concatenation, preventing type mismatches when building printable inventory labels or stock sheets.

Common errors

Which VALUETOTEXT error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	The format parameter is not a valid number or is an unrecognized format code (e.g., passing text like "decimal" instead of the numeric code 2).	Use a numeric format code; common codes are 0 (General), 2 (two decimals), 14 (date). Remove any text and pass only the number.
#NUM!	The format parameter is a number but outside the valid range (typically format codes 0–49 are valid; codes beyond this range are rejected).	Use a format code within the valid range. Consult Excel's number-format-code reference or test codes 0–49 to find the format you need.
#REF!	The value parameter references a cell or range that has been deleted, moved, or is otherwise invalid (e.g., =VALUETOTEXT(Z999999) where that cell is outside bounds or has been cleared).	Verify the cell reference exists and contains data. Update the formula to reference a valid cell, or use a literal value (e.g., =VALUETOTEXT(3.2) instead of a broken reference).

Tips and when to use something else

Use VALUETOTEXT when exporting inventory data to ensure numbers and dates format consistently before concatenation or export—format codes guarantee the text output matches your requirements.
For simple text conversion without specific formatting, the & operator alone is faster. Reach for VALUETOTEXT only when format codes are needed (e.g., "show exactly 2 decimals" or "print dates as M/D/YYYY").
If you need to convert an entire array to text (not a single value), use ARRAYTOTEXT instead—VALUETOTEXT works only on single values.
VALUETOTEXT is available only in Excel 365; if sharing spreadsheets with Google Sheets or older Excel versions, use TEXT() function as a fallback, which has broader compatibility.

Related functions

ARRAYTOTEXTARRAYTOTEXT converts an array into a delimited text string, with options to control separator style for easy data export.TEXTTEXT converts a numeric, date, or time value into a formatted text string based on a custom format pattern you supply.CONCATENATECONCATENATE joins several text strings into one continuous string, in the order the arguments are supplied, and returns it as a single text value.INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.XLOOKUPXLOOKUP returns the value from a specified return array that corresponds to a given lookup value, with optional handling for missing matches and search direction.

Frequently asked questions

What is the difference between VALUETOTEXT and the TEXT function?: VALUETOTEXT (Excel 365 only) converts any value type using Excel's built-in format codes and is newer and more flexible. TEXT is older, broader-compatible, and optimized for dates and numbers. For basic date formatting, TEXT is still often simpler; for modern sheets needing precise formatting of mixed types, VALUETOTEXT is preferred.
Can VALUETOTEXT convert an array or range to text?: No. VALUETOTEXT converts a single value only. If you need to convert an entire array of values to text at once, use ARRAYTOTEXT instead, which is specifically designed for ranges.
What happens if I omit the format parameter?: VALUETOTEXT uses General format (code 0) by default, converting the value to its simplest text representation without special formatting. For example, 5.5 returns "5.5" and dates return their serial number or default text. Include a format code only if you need specific formatting like decimals or date layout.
Why does my VALUETOTEXT formula return #VALUE! when I use a custom format code?: VALUETOTEXT does not accept custom format strings like "mm/dd/yyyy"; it only recognizes predefined Excel format codes (0–49). If you need a custom format, use the TEXT function instead, which accepts custom format strings like =TEXT(E2, "mm/dd/yyyy").

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17