The Excel VALUETOTEXT function converts a single value of any type — number, text, logical, even an error — into a text string. The default concise format just returns the value as text; the strict format wraps text in quotation marks, which makes it the fastest way to answer one of Excel’s most maddening questions: is that cell the number 42, or the text "42" that quietly breaks your lookups? Its range-sized sibling is ARRAYTOTEXT.
Syntax
| Argument | Description | |
|---|---|---|
value | Required | The value to convert — a cell reference or a literal. Numbers, text, TRUE/FALSE, and error values are all accepted. |
format | Optional | 0 (default) = concise: the value as plain text. 1 = strict: text values come back wrapped in quotation marks; numbers, logicals, and errors come back bare. |
Available in: Excel for Microsoft 365 and Excel for the web only. Excel 2021 and earlier show #NAME?. Give VALUETOTEXT a whole range in 365 and it spills — one converted value per cell.
Spotting text-numbers and other impostors
Concise format (column B) makes every value look the same. Strict format (column C) tells the truth — row 3’s quotes expose a text-number that SUM would skip and VLOOKUP would miss:
| A | B | C | |
|---|---|---|---|
| 1 | Value in A | =VALUETOTEXT(A2, 0) | =VALUETOTEXT(A2, 1) |
| 2 | 42 (number) | 42 | 42 |
| 3 | 42 (text) | 42 | "42" |
| 4 | Hello | Hello | "Hello" |
| 5 | TRUE | TRUE | TRUE |
Build a one-column audit next to imported data — anything wearing quotes needs fixing:
Concise format shines when stitching mixed types into a message without worrying what each cell holds:
Try it: interactive VALUETOTEXT demo
Pick what cell A2 contains, switch the format argument, and watch where the quotes appear.
Errors & common pitfalls
#NAME? — older Excel. VALUETOTEXT requires Excel for Microsoft 365 or Excel for the web. In Excel 2021 and earlier, the closest stand-ins are &"" concatenation or the TEXT function.
Pitfall: number formatting is ignored. A cell showing $1,234.50 converts to 1234.5, and a date converts to its serial number. VALUETOTEXT reports the underlying value; use TEXT when you need the displayed form.
Pitfall: errors don’t propagate — they convert. Point VALUETOTEXT at a cell containing an error and you get that error’s name as harmless text rather than a failing formula. Handy for logging, but it means IFERROR around the outside won’t catch anything.
Pitfall: concise mode hides exactly what you’re hunting. With format omitted, the number 42 and the text "42" produce identical output. If you’re using VALUETOTEXT to diagnose type problems, always pass 1.
Practice workbook
Frequently asked questions
What's the difference between VALUETOTEXT and TEXT?
How do I tell if a cell holds a number or text that looks like a number?
=VALUETOTEXT(A2, 1). Text comes back in quotation marks ("42"), real numbers come back bare (42). ISTEXT gives a TRUE/FALSE answer, but VALUETOTEXT also shows you the content — including any stray spaces hiding inside the quotes.What's the difference between VALUETOTEXT and ARRAYTOTEXT?
What does VALUETOTEXT return for an empty cell?
=IF(A2="", "[blank]", VALUETOTEXT(A2, 1)).Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class