+ Reply to Thread
Results 1 to 8 of 8

Standardizing Postcodes

  1. #1
    Registered User
    Join Date
    04-17-2008
    Location
    Yorkshire
    Posts
    79

    Standardizing Postcodes

    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!
    Last edited by mikeyfear; 05-14-2008 at 10:00 AM.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202
    Try this
    Please Login or Register  to view this content.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202
    You can prevent input errors by using the worksheet_change event

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    04-17-2008
    Location
    Yorkshire
    Posts
    79

    Thanks!

    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!

  5. #5
    Registered User
    Join Date
    04-17-2008
    Location
    Yorkshire
    Posts
    79

    Nearly there!

    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.

  6. #6
    Registered User
    Join Date
    04-17-2008
    Location
    Yorkshire
    Posts
    79
    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.
    Last edited by mikeyfear; 05-15-2008 at 07:56 AM.

  7. #7
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202
    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.

  8. #8
    Registered User
    Join Date
    04-17-2008
    Location
    Yorkshire
    Posts
    79
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1