I have to analyse 1261 rows by the first 4 characters of their post code (zip code)

So let's say the column values are:

RM7 0AH
RM14 1BB
RM14 2XX
RM6 1PQ

I want an output as below:

RM7 1
RM14 2
RM6 1

I've tried googling but can't think of how to describe what I'm looking for

Is this a one-time-only analysis, that is once you have your counts, you won't be updating the base data? Or is it an interactive list where you will be adding additional post codes to the list and need to keep running totals?

In the first case, it would be simple enough to just reformat the data, getting rid of the RM and the last four characters leaving you with just a list of one and two digit numbers, you could sort them, and then do a simple count of each group of the same number.

I suspect you would have already done this if it were a one-time task. For an interactive list... I ran into the same CountIf difficulties you did. No doubt there is a way to do it, but it will need more excel-fu than I can provide.

tanstaafl.

It's static data, results of a survey. I kind of just had a brain freeze, had a lot of cleaning to do while I was formatting the data.

I'm sure there's a better way to do it but I've just extracted the first 4 characters into a new column then sorted/subtotalled them. I need the first 4 characters (numbers and letters)

Your sample output doesn't match your description. "RM14 2" is not the first four characters. "RM7 1" should be "RM7 0" ?

Is it only 3 or 4 leading characters?

Something like this?
=IF(FIND(" ",A1)=4, LEFT(A1,5),LEFT(A1,6))

If the space is completely variable, use the location of the space from FIND to get two substrings and put a space in the middle.

Yeah, just saw it doesn't match

Will try that, although I did get it done in the end