How to quickly use VALUETOTEXT function in Excel 365?

Introduction

When working with extensive spreadsheets that comprises numerous columns and rows, finding specific information can become challenging without proper visibility cues. Manipulating large voluminous of unstructured data is very time-consuming for the users. A wide variety of fourteen new Excel functions are introduced in Excel 365 to ease the user's work. To overcome this problem, users may utilize the new inbuilt functions like Filter, Unique, Lambda, Xlookup, etc.

In this article, we will unravel the hidden concept of the VALUETOTEXT function. This function is not available in the older version of the Excel tool.

VALUETOTEXT Function

It extracts the text from the specified cell address.

Syntax

VALUETOTEXT(cell address,[format])

It consists of two arguments −

• The first argument specifies the cell address that comprises the value to return a certain text.

• The second argument consists of two values either 0 or 1 and is optional.

  • 0 − It is the default value and the retrieved text would remain the same.

  • 1 − The resulting text would be embedded in the double quotes. But if the resulting text is a Boolean value, Errors, and in numeric form, in that case, double quotes will not be used.

VALUETOTEXT Function in Excel 365

Step 1

You need to develop a table consisting of two columns named “Sample Data” and “Formula”. Enter a few mixed entries in the first column as shown below image −

Step 2

Enter the formula =VALUETOTEXT([@[Sample data]],1) in the B4 cell. Here, we employ the strict formatting value that is 1 in the second argument of the function. Evaluating the formula in the table is very interesting. If the VALUETOTEXT formula is written in one cell then the same formula will be automatically applied to the remaining cell of the column.

Step 3

Press the “Enter” tab to retrieve the computed text value as highlighted in below image −

As you see in the above image, after applying the VALUETOTEXT function, only text data that is “David John” and “World Cup” are enclosed in double quotes whereas other sample data remain the same.

Notice that the resulting text value remains the same even if we use the value 0 for the second argument. If the second argument is other than 1 or 0 then in that case, error may occur.

Conclusion

The step-by-step screenshots along with the thorough explanation are given in this article. It assists users in managing and retrieving qualitative and meaningful data.