split - Is there a way to specify an input is a single cell in Google Sheets?

Question

I want to iterate over an array of cells, in this case B5:B32, and keep the values that are equal to some reference text in a new array.

However, SPLIT nowadays accepts arrays as inputs. That means that if I use the array notation of "B5:B32" within ARRAYFORMULA or FILTER, it treats it as a range, rather than the array over which we iterate one cell at a time.

Is there a way to ensure that a particular range is the range over which we iterate, rather than the range given at once as an input?

What I considered was using alternative formulations of a cell, using INDEX(ROW(B5), COLUMN(B5)) but ROW and COLUMN also accept array values, so I'm out of ideas on how to proceed.

Example code:

ARRAYFORMULA(
    INDEX(
        SPLIT(B5:B32, " ", 1), 1
    ) = "Some text here"
)

Example sheet: https://docs.google.com/spreadsheets/d/1H8vQqD5DFxIS-d_nBxpuwoRH34WfKIYGP9xKKLvCFkA/edit?usp=sharing

Note: In the example sheet, I can get to my desired answer if I create separate columns containing the results of the SPLIT formula. This way, I first do the desired SPLITS, and then take the values I need from that output by specifying the correct range.

Is there a way to do this without first creating an output and then taking a cell range as an input to FILTER or other similar functions?

For example in cell C35 I've already gotten the desired SPLIT and FILTER done in one go, but I'd still need to find a way to sum up the values of the first character of the second column. Doing this requires that I take the LEFT value of the second column, but for that I need to output the results and continue in a new cell. Is there a way to avoid this?

question from:https://stackoverflow.com/questions/65908201/is-there-a-way-to-specify-an-input-is-a-single-cell-in-google-sheets

Answer 1

Ralph, I'm not sure if your sample sheet really reflects what you are trying to end up with, since, for example, I assume you are likely to want the total of the hours per area.

In any case, this formula extracts all of the areas, and the hours worked, and is then easy to do further calculations with.

=ArrayFormula({REGEXEXTRACT({C5:C9;D5:D9;E5:E9;F5:F9;G5:G9;H5:H9},"(.*) d"),
         VALUE(REGEXEXTRACT({C5:C9;D5:D9;E5:E9;F5:F9;G5:G9;H5:H9}," (d+)hrs"))})

Try that in cell E13, to see the output.

The first REGEXEXTRACT pulls out all the text in front of the first space and number, and the second pulls out all the digits in a string of " #hr" in each cell. These criteria could be modified, if necessary, depending on your actual requirements. Note that it requires the use of VALUE, to convert the hours from text to numeric values, since REGEXEXTRACT produces text (string) results.

It involved concatenating your multiple data columns into one long column of data, to make it simpler to process all the cells in the same way.

This next formula will give you a sum, for whatever matching room/task you type into B6, as an example.

=ArrayFormula(QUERY({REGEXEXTRACT({C5:C9;D5:D9;E5:E9;F5:F9;G5:G9;H5:H9},"(.*) d"),
               VALUE(REGEXEXTRACT({C5:C9;D5:D9;E5:E9;F5:F9;G5:G9;H5:H9}," (d+)hrs"))},
     "select Col1, sum(Col2) where Col1='"&B6&"' group by Col1 label sum(Col2) '' ",0))