Excel VALUETOTEXT Function – How To Use

The VALUETOTEXT function is used to convert any value to text format. If the input value contains numeric formatting such as date or time, the resulting data loses the format.

The VALUETOTEXT function is worthwhile when we wish to gain compatibility with other spreadsheet programs, especially when the resulting data needs to be further processed.

Syntax

The syntax of the VALUETOTEXT function contains two arguments. The syntax to use the function is as follows.

Arguments:

The details of both arguments are given below.

'value' – This is a required argument that accepts the input data that we wish to convert to text. It can be a direct value enclosed in double quotes or a cell reference.

'format' – This is an optional boolean argument that determines whether the resulting data will be in a concise or strict It accepts 0 or 1 as its input value. The default value of the format argument is 0, which translates to a 'concise' format. It is the normal format that Excel uses for text display. When the input value is set to 1 or 'strict', the resulting data is enclosed in double quotes ("") with a few exceptions discussed below.

Important Characteristics of the VALUETOTEXT Function

Some of the noteworthy features of the VALUETOTEXT function are as follows.

• If the data in the value argument contains a boolean answer, numbers, or error messages, the VALUETOTEXT function does not add double quotes in the resulting text.
• If the value of the format argument is anything except 0 or 1, the VALUETOTEXT function results in a #VALUE! error.

Examples of VALUETOTEXT Function

With the help of the below-mentioned examples, let's understand how the function behaves with different input values.

Example 1 – Simple Use of VALUETOTEXT Function

In this example, we have taken different input data for the first argument value and then decided to check the return value for both 0 and 1 as the input value for the second argument format.

The formula used will be as follows.

The first two examples contain a currency value and date value, respectively. As observed, in both cases, the VALUETOTEXT function converts the data into text form and removes the original formatting. As dates are stored in Excel as sequential numbers, the function returns the same.

The next two examples are simple numbers, one in a numerical format and the other in a text format evident by their alignment. Both the numbers are simply transformed to text format, as is evident by the left alignment of values in cell C6:D7.

The following example in cell B8 is a simple text, 'Excel'. The return value with the concise format is the same as the input value. However, the return value with strict format is the word 'Excel' enclosed in double quotes as seen in cell D8. The subsequent example will further clarify the function.

The input value in cell B9 is a text enclosed in double quotes. The concise format result is as expected, which is the same as the input value as seen in cell C9. The resulting data when the value of the format argument is set to 1 is the input value wrapped in another set of double quotes which is called escape characters. We can see the resulting data in cell D9.

The VALUETOTEXT function ensures that the resulting data is written in such a way that it can be understood when further processed hence, values such as booleans (True/ False) and errors are returned as it is in both concise and strict format.

Now that we have clarity on how the VALUETOTEXT function behaves with different input values, we can proceed further to explore some applications.

Example 2 – Adding Double Quotes Using VALUETOTEXT Function

In this example, we downloaded a few famous quotes to publish on social media or add to our newsletter. However, each quote is followed by its author. We only wish to publish the quote in double quotations.,

As each quote is followed by a dash and the author's name, we can use the TEXTBEFORE function to extract the text that occurs before the dash and then use the VALUETOTEXT function to transform them into text form with a 'strict' format which will wrap the return value in double quotes.

To extract the quote from the input text, the formula will be as follows.

Now that we have the quotes in a separate column, we can apply the VALUETOTEXT function. The final formula to be used is as follows.

Now we have the desired quotes in double quotation marks that we can share further for intended purposes.

Another great application of the VALUETOTEXT function is when we wish to join text from different cells into one while adding double quotes to each. Let's explore it further using an example.

Example 3 – Combining Text Using VALUETOTEXT Function

In this scenario, we have received a list of the most used TikTok hashtags for business users. We now wish to combine them as required in a single sentence to share them in common groups.

For instance, if we wish to send the top five hashtags to the marketing team, we will use the TEXTJOIN function along with the VALUETOTEXT function to combine them and form a sentence.

The formula used will be as follows.

Here, we are first adding the top five hashtags (cell B3 to B7) in the VALUETOTEXT function to ensure that each hashtag is enclosed in double quotes for better clarity. We then add the return value in the TEXTJOIN function, where we wish to combine each hashtag with a comma while omitting the empty cells.

Now that we have all the hashtags combined, we can simply share them on the intended channels. With the use of the VALUETOTEXT function, we were able to distinguish each hashtag as they are enclosed in double quotes properly.

VALUE Function vs VALUETOTEXT Function

By now, we know that the VALUTOTEXT function transforms the data to text form, whereas the VALUE function converts a number in text format to numerical format.

One major difference between the two is that the VALUE function only accepts input data that is Excel recognized. For all other types of data, it returns a #VALUE! error.

The VALUETOTEXT function displays the data in a format that is easy to analyze and process, hence the double quotes in text data. In contrast, the VALUE function simply transforms the format for better compatibility and ease of calculations.

Hopefully, now you understand the complete functionality of the VALUETOTEXT function and how to use it to your advantage. Discover more ways to use the function while we bring to you another useful Excel function.