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.
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.
Table of Contents
1. VALUETOTEXT Function Syntax
VALUETOTEXT(array, [format])
2. VALUETOTEXT Function Arguments
| 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. VALUETOTEXT Function examples
This example shows the VALUETOTEXT function with a 0 (zero) as the second argument meaning concise format.
Formula in cell C3:
Note, all values in column D are converted to text and are automatically left-aligned in the cells.
This example demonstrates how the VALUETOTEXT function returns values in strict form.
Formula in cell C3:
Text values have leading and trailing double quotes, remaining values are unchanged.
4. VALUETOTEXT 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 30 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