VALUETOTEXT Function

The VALUETOTEXT function converts a value to a text string. By default, text values pass though unaffected. However, in strict mode, text values are enclosed in double quotes (""). VALUETOTEXT will always remove number formatting applied to numeric values regardless of format.

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:

=VALUETOTEXT(A1) // returns "100"
=VALUETOTEXT(A1,0) // returns "100"
=VALUETOTEXT(A1,1) // returns "100"

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:

=VALUETOTEXT(A1) // returns "apple"
=VALUETOTEXT(A1,0) // returns "apple"
=VALUETOTEXT(A1,1) // returns ""apple""

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.