Standardizing Postcodes

**mikeyfear** · 05-14-2008, 09:24 AM

I have a file of 60000 postcodes, which I've created a page to sum up certain ranges that we're interested in.

The postcodes should all be in the format AA11 1AA, but the people entering in the info are not very conistant. Is there anyway I can have a macro check column B for each cell to ensure that it's in the format AA11 1AA, and where its been entered as AA111AA, add the missing space?

*edit*
I forgot about postcodes like A11 instead of AA11, and AA1 instead of AA11. The space therefore needs to be inserted 3 from the right, if their isnt one already. Any other spaces in the cell might as well be deleted while we're at it!

**royUK** · 05-14-2008, 10:28 AM

Try this

Please Login or Register  to view this content.

**royUK** · 05-14-2008, 10:34 AM

You can prevent input errors by using the worksheet_change event

Please Login or Register  to view this content.

**mikeyfear** · 05-14-2008, 10:48 AM

Thanks again Roy.

They're not input here, but spat out of our customers delivery system in 64000 row chunks. Really not friendly to work with it at all, I will need some help again shortly I'm sure, because my unefficient methods of working dont agree with so much data, I'm crashing the PC every 5 mins at the moment... but I digress. Thanks!

**mikeyfear** · 05-15-2008, 03:10 AM

I have update your code Roy to include the 5 and 6 digit postcodes that could be missing spaces too, see below:

Please Login or Register  to view this content.

This almost works but if the postcode is a correctly formatted char one like:
B6 2JB instead of a B662JB, it puts an uneeded extra space in the post code. Is it possible to have the macro ignore any cell entry where the forth char from the right is already a " ". I tried and failed to acheive this.

**mikeyfear** · 05-15-2008, 07:51 AM

Smug face, I may have managed to help myself for a change. This appears to do what I want to. I shall get the hang of this vba malarky eventually. Code for reference:

Please Login or Register  to view this content.

Seems to work, so I'll leave that up as an example should anyone want to do the same. Thanks for the inital help.

**royUK** · 05-15-2008, 08:05 AM

It's good to see someone trying to adapt the code. Yoiu can shorten yours by moving the check

Please Login or Register  to view this content.

**mikeyfear** · 05-15-2008, 08:56 AM

I tried it there but it would need to look for the 4th char from the right, not the left.

I couldn't find what to use to give me that , so if I put it in each specific case since in a 7 char string the 4th from the left = the 4th from the right, and in a 6 char string the 3nd from the left will always be the 4th from the right.

Otherwise I don't think your version would skip the likes of B1 1BB, i.e. 6 chars and the space ought to live in the 3rd char.

Standardizing Postcodes

LinkBack

Thread Tools

Rate This Thread

Display

Standardizing Postcodes

Thanks!

Nearly there!

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions