1

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.

2
  • 2
    Welcome. "So far I can only get it to search a single cell, but not a range of cells,". Please edit your question and share the formula you use. Sep 21, 2020 at 3:38
  • Please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. Oct 9, 2020 at 9:18

1 Answer 1

0

You mentioned

=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.

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

=QUERY(ArrayFormula(IFERROR(REGEXEXTRACT(A1:A11,"#(.*)"))), "where Col1 is not null")

You will then need to clear all cells in range D2:D and apply the following on cell D2

=ARRAY_CONSTRAIN(SORT(F2:F,SORT(ROW(F2:F),MOD(ROW(F2:F),2),1),1),COUNTA(F2:F)*2,1)

Insert an empty row every other row

Using the same technique you can have the Year column in just one go.

Functions used:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.