Here, we will learn how to use the Excel dynamic array functions: ARRAYTOTEXT and VALUETOTEXT.
ARRAYTOTEXT function
The Excel ARRAYTOTEXT function converts the given array or range to a text string that can include curly braces. For example, to convert the array {"a","b","c"} to text, use ARRAYTOTEXT like this: =ARRAYTOTEXT({"a","b","c"}) that returns "a, b, c". To include curly braces in the result, set the second argument to 1, like this: =ARRAYTOTEXT({"a","b","c"},1) that returns "{"a","b","c"}". The function can be found in Excel 365 - Excel 2021. ARRAYTOTEXT takes the arguments like this: (array, [format]), of which array is required. Array accepts the array or range that needs to be converted to text. Format controls how the structure of the result will be displayed. It accepts either 0 or 1. Setting 0 will display each value separated with comma (,). This format is known as the "concise" format. Setting 1 will display each value enclosed in double quotes (""), separated with semicolon (;) for rows and comma (,) for columns, and will include the result in the curly braces. This format is known as the "strict" format. Omitting the argument defaults to 0. Note that the function does not encloses numeric values and Boolean values (TRUE/FALSE) in double quotes.
VALUETOTEXT function
The Excel VALUETOTEXT function converts the given value to a text string that can include double quotes (""). For example, to convert the value "a" to text, use VALUETOTEXT like this: =VALUETOTEXT("a") that returns "a" without double quotes. To include double quotes in the result, set the second argument to 1, like this: =VALUETOTEXT("a",1) that returns ""a"" with double quotes. The function can be found in Excel 365 - Excel 2021. VALUETOTEXT takes the arguments like this: (value, [format]), of which value is required. Value accepts the value that needs to be converted to text. Format controls how the structure of the result will be displayed. It accepts either 0 or 1. Setting 0 will display the value in the text format. This format is known as the "concise" format. Setting 1 will display the value enclosed in double quotes (""). This format is known as the "strict" format. Omitting the argument defaults to 0. Note that the function does not enclose numeric values and Boolean values (TRUE/FALSE) in double quotes, when format is set to 1.
You have successfully learnt how to use the Excel dynamic array functions: ARRAYTOTEXT and VALUETOTEXT.
