How to use the VALUETOTEXT function
What is the VALUETOTEXT function?
The VALUETOTEXT function returns a value in text form. Text values are unmodified and non-text values are converted to text.
Table of Contents
1. Introduction
What is a text value?
Excel has three different types of values: Text, numbers, and Boolean values. Excel tries to identify values you enter in a cell and sometimes it gets it wrong.
Excel shows what kind of value it is by aligning the value to the right, center or left.
What are dates and time values?
They are also numbers but formatted differently. The image below demonstrates different values in Excel.
- Cell B3 contains a text value, it is left-aligned in the default setting.
- Cell B4 contains a whole number, it is right-aligned in the default setting.
- Cell B5 contains a boolean value, it is centered in the default setting.
- Cell B6 contains a rational number, it is also right-aligned in the default setting.
- Cell B7 contains a boolean value, it is centered in the default setting.
- Cell B8 contains an error value, it is centered in the default setting.
- Cell B9 contains a time value which is a number formatted as a time value, it is right-aligned in the default setting.
- Cell B10 contains a date value which is a number formatted as a date value, it is right-aligned in the default setting.
What is the difference between VALUETOTEXT function and ARRAYTOTEXT function?
The VALUETOTEXT function is for converting individual values to text while the ARRAYTOTEXT joins arrays or values in a cell range into an output text string.
Use the VALUETOTEXT function if you want to convert a single value and the ARRAYTOTEXT function if you want to convert multiple values.
2. Syntax
VALUETOTEXT(array, [format])
| array | A cell reference or an array. |
| [format] | Optional. This argument lets you change the output format.
0 - Default. Concise format, the returned value has general formatting applied. 1 - Strict format, includes escape characters. Encloses all values with quotes except for errors, numbers, and boolean values. |
3. Example 1
This example shows the VALUETOTEXT function with a 0 (zero) as the second argument meaning concise format. Note, all values in column D are converted to text and are automatically left-aligned in the cells.
Formula in cell D3:
Cell B3 contains "How are you?" which is a text value. The VALUETOTEXT function in cell D3 converts it to a text value, in this case, it is already a text value.
Formula in cell D4:
Cell B4 contains 5, which is a numeric value. The VALUETOTEXT function in cell D4 converts it to a text value "5" without double quotes.
Formula in cell D5:
Cell B5 contains TRUE, which is a logical value. The VALUETOTEXT function in cell D5 converts it to a text value "TRUE" without double quotes.
Formula in cell D6:
Cell B6 contains 3.12, which is a numeric value. The VALUETOTEXT function in cell D6 converts it to a text value "3.12" without double quotes.
Formula in cell D7:
Cell B7 contains FALSE, which is a logical value. The VALUETOTEXT function in cell D7 converts it to a text value "FALSE" without double quotes.
Formula in cell D8:
Cell B8 contains #N/A, which is an error value. The VALUETOTEXT function in cell D8 converts it to a text value "#N/A" without double quotes.
Formula in cell D9:
Cell B9 contains 5:42 AM, which is a time value. The VALUETOTEXT function in cell D9 converts it to a numeric value representing the fraction of a day, displayed as "0.2375" without double quotes.
Formula in cell D10:
Cell B10 contains 1/2/2025, which is a date value. The VALUETOTEXT function in cell D10 converts it to a numeric value representing the number of days since January 1, 1900, displayed as "45659" without double quotes.
4. Example 2
This example demonstrates how the VALUETOTEXT function returns values in strict form. Text values have leading and trailing double quotes, remaining values are unchanged.
Formula in cell C3:
The text "ZF" in cell B3 is transformed by the VALUETOTEXT function in C3. This function, with its two parameters (array and format), employs the strict format to encase the original text within double quotation marks.
Formula in cell C5:
Cell C5 displays the result of the VALUETOTEXT function applied to the number 5 from B5. The strict format used in this function preserves numeric values without adding quotation marks.
Formula in cell C6:
In C6, we see the outcome of VALUETOTEXT processing "T5" from B6. The function's strict format parameter ensures that this text input is returned enclosed in double quotes.
Formula in cell C8:
The error value "#N/A" in B8 remains unchanged when processed by VALUETOTEXT in C8. The function's strict format leaves error values intact, without adding quotation marks.
Formula in cell C9:
VALUETOTEXT in C9 processes the text "SK" from B9. Using the strict format, it returns the input surrounded by double quotation marks.
Formula in cell C10:
Similarly, cell C10 shows "JK" in quotes, resulting from VALUETOTEXT's strict format application to the text in B10.
Formula in cell C12:
Cell B12 contains "DP" which is a text value. The VALUETOTEXT function in cell C12 has two arguments, array and [format]. This example uses the strict format which converts the source data to a text value enclosed in double quotes.
Formula in cell C13:
Cell B13 contains 3 which is a numeric value. The VALUETOTEXT function in cell C13 has two arguments, array and [format]. This example uses the strict format which leaves the numeric value as is without enclosing it in quotes.
Formula in cell C14:
Cell B14 contains "#DIV/0!" which is an error value. The VALUETOTEXT function in cell C14 has two arguments, array and [format]. This example uses the strict format which leaves the error value as is without enclosing it in quotes.
5. Function not working
VALUETOTEXT returns a #VALUE! error if the second argument [format] is larger than 1 or smaller than 0 (zero).
The second argument in the formula in cell D3 is 2, the VALUETOTEXT function returns #VALUE! shown in the image above.
A #NAME! error is displayed if you misspelled the VALUETOTEXT function.
Useful links
VALUETOTEXT function - Microsoft support
VALUETOTEXT
Functions in 'Text' category
The VALUETOTEXT function function is one of 29 functions in the 'Text' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form