How to take formula inputs to a UDF like conditional formatting

Question

Imagine you want to check whether the left letter of each word in a range is "a", then join the words for which that condition is true. One way is with a helper column, returning "" if not true, the word if it begins with "a", and then a total row which CONCAT()s over the helper column.

Another way would be to use an array formula. {=CONCAT(IF(LEFT(range) = "a", range, ""))}. That's effectively using a helper column anyway.

But what I want is to use the conditional formatting approach: When applying conditional formatting, you reference the top left cell of your range. So for a range in A1:A10, I want a function like =CONCATIF(A1:A10, LEFT(A1) = "a"). SUMIF and COUNTIF get close to this functionality, only they use string inputs ">", "=..." which cannot reference the cell which they are testing (i.e., there is no equivalent to LEFT(A1) since there is no way of referring to the_cell_I'm currently_working_on in the string)

But the problem is, if I make a UDF which tries to accept a test like LEFT("A1") = "a", it just evaluates for that one cell, not over the whole range like Conditional formatting does.

Why not just use an array formula?

Well mainly I'm intrigued to see whether a vba function can act as an array formula in the way COUNTIF does - by evaluating some condition specified as a parameter. Only with the functionality of conditional formatting in accepting relative formulae, not string, inputs.

But also, for this specific case, I'm trying to CONCAT some long paragraphs into a single string. Each paragraph is maybe 1000 characters, and for whatever reason, {=CONCAT(IF(LEFT(A1:A10) = "a", A1:A10, ""))} won't work since IF( returns a value error for the long paragraphs. The formula works for small paragraphs. But a VBA version can concat the whole of A1:A10, I just need a way to run the test condition first!

Update

---

To make it explicitly clear, I'm looking for the following:

• A generic UDF which takes a RANGE as well as a Boolean FORMULA input
  • These can be in the same parameter of the UDF if required
• The UDF evaluates the Boolean formula on each of the cells in the range
• The UDF returns an array of TRUE and FALSE values based on the result
• This array can be passed to standard array handling functions like CONCAT or SUM
  • Or if an array-returning UDF is not possible, the array can be passed internally to another portion of the UDF which then handles it
  • Similar to how COUNTIF must return an array of TRUE/FALSE at some point, but then counts them internally to return a number, not an array, and can therefore be entered not as an array formula.
• I'm interested in how to generate this array of Booleans

Now research shows there are 3 main ways: Array functions, which for whatever reason aren't able to hold long strings, CONCATIF/SUMIF type functions, which take a text parameter as the boolean test, but which are heavily limited in the types of test. The third approach is the one that Conditional formatting uses; a range is selected and the formula test runs over all of the cells in the selected range, returns TRUE or FALSE (internally), and this array of booleans is used to format the cells in the range. But in a UDF they could just as easily be passed as a result or used internally for further calculations (e.g. a CONCATIF function)

Consequently, I'm looking for one of the following:(this is instead of an array formula approach, and should ideally be entered without ctrl+shift+enter.)

• A UDF which uses an approach like COUNTIF but better: it accepts a range parameter and a formula as a string, the formula being any Boolean-returning worksheet formula such as 'LEFT(cell)="a" (note, a text string)
• Better a UDF which uses an approach like conditional formatting; accepts a Range and any boolean-returning formula *not formatted as text"
  • That would be nice as you can use the Excel auto-prompt and range highliting to create the formula, which you couldn't do if it were text
• For option 1), the boolean-formula can reference the word "cell" or the range over which it's acting. For option 2) it references the top-left cell in the range, like in conditional formatting
  • I'm thinking application.caller might be useful in extracting the formula part when it's not text.
• The UDF could end up returning a number of options, could be tailored further with worksheet functions or with custom calculations, but as I say, I'm really after the list of booleans

I didn't type that at first because I didn't want to make a code this for me question, but I think I've provided enough detail there (and proof of independant thought!) that it's now a matter of how not what, which I believe is more acceptable on SO.

Answer 1

Use the newer TEXTJOIN¹ and IF in an array formula with CSE.

=TEXTJOIN(CHAR(32), TRUE, IF(LEFT(A1:INDEX(A:A, MATCH("zzz", A:A)))="a", A1:INDEX(A:A, MATCH("zzz", A:A)), ""))

---

¹ _{The TEXTJOIN was introduced with Excel 2016 in the following versions:Excel for Android phones, Excel Mobile, Excel 2016 with Office 365, Excel 2016 for Mac, Excel Online, Excel for iPad, Excel for iPhone and Excel for Android tablet. If your version of Excel does not support TEXTJOIN (e.g. you receive a #NAME! error) then search this site for [excel]textjoin for alternatives.}