Edit for special filter and count...

**superspurs** · 03-10-2009, 07:27 AM

Ok,,, I've got a spreadsheet with over 60,000 entries each with a different post code.

I want to filter and then count to find the most re-occuring post code.

However, I want to do this just on postal area, so I need to edit the info so the last 3 characters of the post code are removed, eg...

SE10 4BX

becomes

SE10

other than doing it manually is there anyway to just command excel to delete last 3 characters from my list?

**NBVC** · 03-10-2009, 07:49 AM

Originally Posted by superspurs

...

However, I want to do this just on postal area, so I need to edit the info so the last 3 characters of the post code are removed, eg...

SE10 4BX

becomes

SE10

other than doing it manually is there anyway to just command excel to delete last 3 characters from my list?

Try using this formula in an adjacent column to get rid of last 3 chars

=TRIM(LEFT(A2,LEN(A2)-3)) where A2 is first postal code to strip.

copy it down

Then copy the whole column and do an Edit|Paste Special >> Values over the old data... then delete the newly added column.

**superspurs** · 03-10-2009, 10:16 AM

Thats really helpful, thank u.

Looking at this again, and this is more the fault of the data than anything,

could i do something similar but say just leave first 2?