Search code examples
arraysexcelexcel-formula

Excel - Pass Multiple Arguments to a Function as a Single String

I'm grasping for straws with this one.

Is it possible to force an Excel formula to process a string as a comma delimiter?

For example, in

=COUNTIF(range, criteria)

pass [$A$1:$A$10, "apple"] as range and criteria as a single string or array. I've tried using TEXTSPLIT() and INDIRECT() to no avail. Would there happen to be a DELIMITER() function that I just haven't heard about?

In other words, is there a way to force Excel formulas to recognize "," as a delimiter in some way, or pass an array of arguments that circumvent Excel's comma requirement?

As to why, my company uses an Excel add-in with its own functions that unfortunately cannot be recognized or called in the Excel Labs environment. In my use case, I have one such that takes 6 required parameters, that I would like to pass a single argument to, in the form of a custom lambda function. This immensely improves user-friendliness for a huge amount of applications within my company.

Solution

  • Right, have a go with this:

    Step 1: Create a named function in the name manager making use of the ancient Excel 4.0 Macro function EVALUATE():

    enter image description here

    Step 2: Now, call it using for example:

    enter image description here

    Please don't mind my semi-colons since I have a Dutch installment of Excel. Just swap these out for the comma's.

    Also note that for me it only worked when I removed the space in front op "apple".

    And a final note; this workbook will now have to be saved with macro's enabled (".xlsm")!