I Included a Google Sheets link
=REGEXEXTRACT(A2:A3, "19..")
To me, it would seem as if this would check both cells for any date starting with 19
and extract that into the cell
But, it doesn't work out.
Much appreciated if anyone can point me in the right direction.
Best Answer
You mentioned
As you found out it does not work that way.
REGEXEXTRACT
works per row.So you would need
=REGEXEXTRACT(A3,"#(.*)")
On top of that, you would have to use the above formula on every row where an RC number is found.
How to insert an empty row, every other row.
The use of advanced formulas.
Instead you can use advanced formulas to achieve the same results for all rows (present and future).
You will have to create -anywhere in your sheet- a helper column using the formula
You will then need to clear all cells in range
D2:D
and apply the following on cellD2
Using the same technique you can have the
Year
column in just one go.Functions used:
QUERY
ArrayFormula
IFERROR
REGEXEXTRACT
ARRAY_CONSTRAIN
SORT
MOD
ROW
COUNTA