The VALUETOTEXT function takes two arguments: value and format. Value is the value to convert to text. The format argument controls the structure of the output. By default, format is zero and VALUETOTEXT will output a “concise” format text value, essentially the normal format that Excel will use to display any text value. When format is set to 1 (strict format), text values will be enclosed in double quotes (""). Note: the ARRAYTOTEXT function performs the same kind of text conversion on arrays.
With numeric values
With the value 100 in cell A1: In all cases, 100 is returned as a normal text string, and you will not see double quotes (") in the output on a worksheet. However, you will see the output aligned left in cells with the General number format applied, since text values appear aligned left in Excel by default. If any number formatting (i.e. currency, percentage, etc.) has been applied to cell A1, it will be lost in the conversion.
With a text values
With the text “apple” in cell A1: Notice in the first two examples above, the text “apple” passes through unchanged. In the third example, where format is set to 1 (strict), double quotes are added to the text and will display on the worksheet.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
