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
D1 downwards to cover the data output of the formulas in
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
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
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.