Google-sheets – Google Sheets TIMEVALUE can’t parse time string

One way to implement something like this is

=VALUE(INDEX(FILTER(SPLIT(A1, " "), ISNUMBER(SPLIT(A1, " "))); 1; 1))

The result of this formula for some example strings is given below.

You may have to modify this to suit your individual needs. It should work pretty well for strings as simple as "3 cups".

• SPLIT(..., " ") splits a string into its space-separated parts. You can add more characters to the string, e.g. " -/", to split whenever those characters are found.

• ISNUMBER checks if a string is a number.

• FILTER takes the array and filters out everything that doesn't satisfy the second function, i.e., it takes out everything that isn't a number.

• INDEX(...; 1; 1) returns the value in the first row, first column of the array (so we only get the first number listed, not all the numbers listed).

• VALUE probably won't be necessary, but just for good measure it converts the final string into a numeric value.

When using query with importrange, the imported columns must be referred to as Col1, Col2, Col3, etc, according to their positions in the imported range. So, you should replace

"select D where V = 1" 

with

"select Col4 where Col22 = 1" 

The reason is that imported range is not considered a part of any sheet, so its column names are not like sheet column names.

To find the number of a column without reciting the alphabet, you may want to put =column() in it temporarily.