In Google Sheets, can you apply a regex to a generated range?

Question

I have a list of UK postcodes, looking roughly like this. Entries may not be unique.

A1 1AA
A1 2BC
A1 3PN
B2 4XY
B2 5LH
C3 6NJ

They're in a range named Postcodes. I've set up the following arrangement to count how many instances of each postcode area (the part before the space) there are.

   A                 B (range "PostcodeAreas")        C                       D
1  =SORT(Postcodes)  =REGEXREPLACE($A1, "\s\w+", "")  =UNIQUE(PostcodeAreas)  =COUNTIF(PostcodeAreas, $C1)

I've extended the formulas in B1 and D1 downwards to cover the data output of the formulas in A1 and C1.

This works, and the output would be this for my example data:

   A       B    C   D
1  A1 1AA  A1   A1  3
2  A1 2BC  B2   B2  2
3  A1 3PN  C3   C3  1
4  B2 4XY
5  B2 5LH
6  C3 6NJ

Columns A and B, being workings, aren't relevant to the target audience of the sheet, so I've hidden them. The end result is a nice set of counts in columns C and D.

All well and good. However, I can't help feeling that this can be optimized a bit. Particularly, I don't like the manually-extended formula in B1. Is it possible to write a single formula that applies the regular expression to all of the SORT() output? Which I could then perhaps even wrap in the UNIQUE(), and eliminate the need for working columns entirely? I've searched around a bit but couldn't quite find an answer.

Answer 1

Assuming your data is in A1:A, try in B1

=query(ArrayFormula(iferror(regexextract(A1:A, {"^(.+)\s", "\s(.+)$"}))), "select Col1, count(Col2) where Col1 <>'' group by Col1")