Forum menu
As this forum seems to be the font of all knowledge, could one of the Excel gurus help me with a formula?
I have a list of around 2,000 members' postcodes of the format BS3 6AA, BS5 0WD etc. I need to calculate the number of members in a single postcode upper area i.e. just BS3 or BS5. I assume you use some form of COUNTIF formula, but I'm stuck on how to just count the postcode upper area.
Thanks in advance!
New column with =left(find(" ",A1)-1) to get the first half of the post code?
You could use the LEFT function
https://www.w3schools.com/excel/excel_left.php
And then COUNTIF using the LEFT results as your wildcards - or just use a filter and count
https://exceljet.net/formulas/count-cells-that-begin-with
Then copy the list of half postcodes, paste them as values in a new column, remove duplicates then use the countif against each one.
I think you can do =countif(A1:A2000,"*BS3*")
New column with =left(find(" ",A1)-1) to get the first half of the post code?
depends on postcode format (i.e. do they actually have the centre space or not?) If they don't you can use =LEFT(A1, LEN(A1) - 3) because (I think) they all end in 3 characters but the first bit can be 2-4 characters.
then another column to find the unique entries =UNIQUE(B1:B100)
and a final column to do the count =COUNTIF(B1:B100, C1)
Alternatively, especially if jobs like this are a one-off, just fling it all at ChatGPT and get it to figure it out. Not being an Excel guru personally, that's basically what I do these days - it's great at analysing and even altering data in XLS, PDF etc! 😃 Generally quicker than figuring out how to do it properly. (Or just get ChatGPT to tell you what formulas to use!)
Screenshot below
A: has list of things to search
B: has this in B1, then dragged down to B20 to auto update the references =LEFT(A1, FIND(" ", A1)-1)
C1: has this which automatically pulls out the unique entries from B1:B20 =UNIQUE(B1:B20)
D: has this in D1, then dragged down to D4 to auto update the references =COUNTIF(B1:B20,C1)
Use the Text to Columns function with space as the delimiter which will split the postcode into 2 columns, then create a pivot table on the column with the 1st column created to count the numbers by postcode area.