A lambda to simplify calculating T values in array expressions in place of using cumbersome IF(ISTEXT) formulas.
Development Rationale
The purpose in developing this lambda is to allow the array non-compliant function T1 to be used on arrays of values. The T function returns a single value, being the text value of the first cell in a referenced range, so therefore cannot be used to return the text values of an array of values. This function overcomes that limitation for an otherwise very handy function.
About the Lambda Calculations
1 =LAMBDA(array_of_values,SCAN(0,array_of_values,LAMBDA(cum,val,T(val))))
In line 1 above, the lambda is defined, with one argument, capitalised as is usual for Excel functions. array_of_values is the array of values to which the T function is to be applied. The lambda calls a SCAN function that simply returns the T value of each cell, and not utilising the capability to accumulate values over the scanned range.
Defined Name Settings
To create the lambda, simply use the define name2 feature in Excel to define this named lambda in the workbook where it will be used. The required settings are shown below and may simply be copied from here and pasted into the Define Name dialog box.
| Name:3 | λArrayT |
| Scope: | Workbook |
| Comment: | A lambda that returns an array of values using the T function, which normally cannot be used in an array expression. |
| Refers To: | =LAMBDA(array_of_values,SCAN(0,array_of_values,LAMBDA(cum,val,T(val)))) |
Syntax of λArrayOr
=λArrayT(array_of_values)
where:
array_of_values is an array of values to which the T function is to be applied.
Sample File
An example file, showing the definition and use of this lambda is available here: Public Lambda Samples.xlsx.
Version History
| Version | Release Date | Release Notes |
| 1.0 | 15/10/2025 | Initial release |
1 The T function returns the text value of a value. It only works on the first value if an array is passed. All text values are returned unchanged, numeric and logical values are returned as an empty string (""). It has the same result as IF(ISTEXT(x),x,"").
2 The required settings are shown below. Ctrl+Alt+F3 is the keyboard shortcut to open the New Name dialog, or you can select Formulas » Define Name.
3 You may change the name of the lambda and drop the prefix. The prefix ensures there is no clash with functions that may be added by Microsoft later or with lambdas from another source. The names given here also tie-in to the GitHub sources for these functions from the Clarkson ITT LAMBDA Library which can be downloaded using the Advanced Formula Environment.