How to delete partially duplicated data

[smashk]

I have a spreadsheet with about 5000 postal codes. I don't need this many because I'm using this to create a map showing approximate delivery locations. So I would like to remove the postal codes which have the same first 3 characters.


For example, here is a sample list:


A0A 2J0
A0A 2M0
A0A 3C0
A0A 4J0
B1P 1A7
B1P 1B9
B1P 1C3


So, I would like to keep the postal codes in bold only.

I have no experience with formulas. Any help is appreciated.


Thanks,

 

[SteveO59L]

Are your codes held in single cells?

 

[smashk]

Yes, one code per cell, in one column, with 5780 rows.

 

[SteveO59L]

ok, assuming they are in col a,

in b1 and fill down =LEFT(A1,3)
in c1 and fill down =COUNTIF($B$1:B1,B1)

then filter for values > 1 and delete those rows

 

[smashk]

Ok, the b1 code works, but the c1 code crashes my Excel (2008!).

Is there a way to keep or export the data in Column B without the formula?

 

[smashk]

I Googled how to keep values without formula and found out. Thank you for your time!

Ak