+ Reply to Thread
Results 1 to 36 of 36

VBA for UK Postcodes

  1. #1
    Registered User
    Join Date
    08-05-2016
    Location
    UK
    MS-Off Ver
    2016
    Posts
    27

    VBA for UK Postcodes

    Hi,

    I have a huge list of data that I need to split into separate postcode areas.

    For example...

    Phone Number - First Name - Last Name - Address - Postcode

    I want to split each postcode area into a different file. BS1 BS2 BS3 BS4 and so on. However, some of the postcodes are in different formats so one might be BS1 1AA and another could be B1 1AA. When I select the postcodes to move to another sheet the other data on the same row has to move as well to stay relevant.

    Is there any easier way for me to do this other than manually selecting each area then copy and pasting?


    Thanks.
    Last edited by hannsg; 08-05-2016 at 08:17 AM. Reason: Title Change

  2. #2
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: UK Postcodes

    Hi
    Can you send a sample of your data?

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,150

    Re: UK Postcodes

    Extracting the start of a UK postcode into a helper column or as part of a formula is not a problem. This will do it:

    =TRIM(LEFT(A1,FIND(" ",A1)))

    However, I think you need a VBA solution to get the data moved.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,777

    Re: UK Postcodes

    It might be easier to sort the data by postcode, and then you can cut/paste into your other sheets.

    Hope this helps.

    Pete

  5. #5
    Registered User
    Join Date
    08-05-2016
    Location
    UK
    MS-Off Ver
    2016
    Posts
    27

    Re: UK Postcodes

    Josť Augusto
    Re: UK Postcodes

    Hi
    Can you send a sample of your data?
    Phone First Name Last Name Add1 Add2 Add3 Postcode
    1234567891 J Smith 100 Scott Street Bristol BS15 4RT
    2345678910 S Thomson 114 Stable Drive Bristol BS4 9QV
    3456789101 M Jackson 17 Maple Avenue Bristol BS19 5HU

    So I want a separate file for each of those areas BS15 BS4 BS19



    Pete_UK
    Re: UK Postcodes

    It might be easier to sort the data by postcode, and then you can cut/paste into your other sheets.

    Hope this helps.

    Pete
    I have it sorted by postcode already but copying and pasting each postcode sector is going to take way too long.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,150

    Re: UK Postcodes

    As I said before, you are likely going to need a macro/VBA solution for this. Try editing the title of your opening post to add VBA into the title, which will hopefully attract the attention of the coders here.

  7. #7
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,843

    Re: VBA for UK Postcodes

    (How to) Upload a Workbook directly to the Forum
    (please do not post pictures or links to worksheets)
    • Click Advanced next to Quick Post button at the bottom right of the editor box.
    • Scroll down until you see "Manage Attachments",
    • Click the "Choose" button at the upper left (upload from your computer).
    • Select your file, click "open", click "upload"
    • Once the upload is completed the file name will appear below the input boxes in this window.
    • Close the Attachment Manager window.
    • Click "Submit Reply"
    Note: Please do not attach password protected workbooks/worksheets
    Ensure to disable any Workbook Open/Autorun macros before attaching!
    Ben Van Johnson

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,777

    Re: VBA for UK Postcodes

    Here's a thread that I contributed to a few years ago for a poster who was asking for a search engine:

    http://www.excelforum.com/excel-gene...readsheet.html

    and it was referred to again in another thread earlier this year:

    http://www.excelforum.com/excel-gene...ine-sheet.html

    It's not exactly the same as your problem, but it does allow you to type a partial postcode in a cell and to display all the records that match in a separate sheet (actually, from a number of different sheets, although you could just specify one sheet). This might be easier/better than having multiple sheets for each postcode area.

    Hope this helps.

    Pete
    Last edited by Pete_UK; 08-05-2016 at 07:32 PM. Reason: Re-done the links

  9. #9
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: VBA for UK Postcodes

    Hi,

    Once you've split your postcode into the Incoming office and the outgoing location then why not use a Pivot Table to analyse your data.

  10. #10
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,843

    Re: VBA for UK Postcodes

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    08-05-2016
    Location
    UK
    MS-Off Ver
    2016
    Posts
    27

    Re: VBA for UK Postcodes

    Thanks for taking the time to write something up for me.

    I am completely new to this kind of thing...

    I hav attached this code to a button using visual basic in the developer setting of excel.

    When I click the button nothing happens.

  12. #12
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,843

    Re: VBA for UK Postcodes

    Post the workbook. I was only guessing at what you are working with.
    (How to) Upload a Workbook directly to the Forum
    (please do not post pictures or links to worksheets)
    • Click Advanced next to Quick Post button at the bottom right of the editor box.
    • Scroll down until you see "Manage Attachments",
    • Click the "Choose" button at the upper left (upload from your computer).
    • Select your file, click "open", click "upload"
    • Once the upload is completed the file name will appear below the input boxes in this window.
    • Close the Attachment Manager window.
    • Click "Submit Reply"
    Note: Please do not attach password protected workbooks/worksheets
    Ensure to disable any Workbook Open/Autorun macros before attaching!
    Attached Files Attached Files
    Last edited by protonLeah; 08-11-2016 at 01:15 AM.

  13. #13
    Registered User
    Join Date
    08-05-2016
    Location
    UK
    MS-Off Ver
    2016
    Posts
    27

    Re: VBA for UK Postcodes

    What you have done is actually exactly what I'm looking for only my data is in separate columns.

    Here is a sample workbook. Sorry it has taken me so long to get back to you.

    Please bare in mind my actual workbook may have up to 500,000 rows in it.


    Thanks
    Attached Files Attached Files

  14. #14
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: VBA for UK Postcodes

    Hi
    I think you not need VBA
    Select column with your data
    Go to Data separator and choice Text to Columns (with comma separator)

  15. #15
    Registered User
    Join Date
    08-05-2016
    Location
    UK
    MS-Off Ver
    2016
    Posts
    27

    Re: VBA for UK Postcodes

    Hi Josť,

    The VBA by ProtonLeah is almost exactly what I need.

    I definitely can not do what I need by doing what you have suggested.

  16. #16
    Registered User
    Join Date
    08-05-2016
    Location
    UK
    MS-Off Ver
    2016
    Posts
    27

    Re: VBA for UK Postcodes

    Really need a solution to this. Would greatly appreciate it.

  17. #17
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: VBA for UK Postcodes

    Maybe this

    Please Login or Register  to view this content.
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  18. #18
    Registered User
    Join Date
    08-05-2016
    Location
    UK
    MS-Off Ver
    2016
    Posts
    27

    Re: VBA for UK Postcodes

    Thanks for the reply mike.

    This is almost what i am looking for... The only issue is that, I only want it sorted on the first section of the postcode.

    For example:

    1224202407 G Peters 12 Morrison Dv Aberdeen AB10 7HD

    gets sorted into a worksheet called AB10 and any other postcodes that start with AB10 also go into that sheet. Whether that be AB10 7HD, AB10 8LA, AB10 9PK.

    I appreciate your time helping me.

  19. #19
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: VBA for UK Postcodes

    this what you want?

    Please Login or Register  to view this content.

  20. #20
    Registered User
    Join Date
    08-05-2016
    Location
    UK
    MS-Off Ver
    2016
    Posts
    27

    Re: VBA for UK Postcodes

    Thanks mike, that's what I'm looking for but...

    When I am trying it with a lot more rows I am getting error subscript out of range. Do you know why this is?

  21. #21
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: VBA for UK Postcodes

    What is highlighted when the code errors?

  22. #22
    Registered User
    Join Date
    08-05-2016
    Location
    UK
    MS-Off Ver
    2016
    Posts
    27

    Re: VBA for UK Postcodes

    .Item(sPostalCode) = w
    Next

    The cursor sits at the end of "Next"

  23. #23
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: VBA for UK Postcodes

    What is the value of cell what is the value of sPostalCode

  24. #24
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: VBA for UK Postcodes

    Quote Originally Posted by hannsg View Post
    Thanks mike, that's what I'm looking for but...

    When I am trying it with a lot more rows I am getting error subscript out of range. Do you know why this is?
    You are getting this error because you are exceeding the maximum number of sheets in a single workbook. Excel support maximum 255 sheets in a single workbook as far as I know.

    If you just want to split the postcodes on attached sample in 2 different column then you can do this easily with those formulas. But actually I am not clear about requirements.
    On I2 write this:
    Please Login or Register  to view this content.
    On J2 write this:
    Please Login or Register  to view this content.
    Now copy them down.

  25. #25
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: VBA for UK Postcodes

    @sanram

    That is not his problem. The line of code that has an error is not creating worksheets yet.

  26. #26
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: VBA for UK Postcodes

    @mike7952
    Actually I couldn't find any error in your code. But hannsg has stated before that his original sheet contains 500000 rows. That's why I have told this. But may be you are right, because I am not so good with array in VBA.

  27. #27
    Registered User
    Join Date
    08-05-2016
    Location
    UK
    MS-Off Ver
    2016
    Posts
    27

    Re: VBA for UK Postcodes

    OK So it only works with up to around 800 rows it won't do much more than that.

  28. #28
    Registered User
    Join Date
    08-05-2016
    Location
    UK
    MS-Off Ver
    2016
    Posts
    27

    Re: VBA for UK Postcodes

    @mike7952

    I have PM'd you but I don't know if my PM's are working as they are not showing in sent box. Let me know if you get it.

  29. #29
    Registered User
    Join Date
    08-05-2016
    Location
    UK
    MS-Off Ver
    2016
    Posts
    27

    Re: VBA for UK Postcodes

    Quote Originally Posted by sanram View Post
    @mike7952
    Actually I couldn't find any error in your code. But hannsg has stated before that his original sheet contains 500000 rows. That's why I have told this. But may be you are right, because I am not so good with array in VBA.
    Hi Sanram,

    I definitely won't be using more than 255 sheets. Most I will ever use is about 80.

  30. #30
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: VBA for UK Postcodes

    As I am not good in array I have created a normal solution for you. Try this:
    Please Login or Register  to view this content.

  31. #31
    Registered User
    Join Date
    08-05-2016
    Location
    UK
    MS-Off Ver
    2016
    Posts
    27

    Re: VBA for UK Postcodes

    again, It seems to work fine for the sample sheet but when I add more rows nothing seems to work.

  32. #32
    Registered User
    Join Date
    08-05-2016
    Location
    UK
    MS-Off Ver
    2016
    Posts
    27

    Re: VBA for UK Postcodes

    Sanram, your solution sorts the first postcode AB1 then an error occurs says subscript out of range.

  33. #33
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: VBA for UK Postcodes

    Problem was PostCode being empty. Code skips that row now

    Please Login or Register  to view this content.
    Last edited by mike7952; 09-04-2016 at 08:17 PM.

  34. #34
    Registered User
    Join Date
    08-05-2016
    Location
    UK
    MS-Off Ver
    2016
    Posts
    27

    Re: VBA for UK Postcodes

    Quote Originally Posted by mike7952 View Post
    Problem was PostCode being empty. Code skips that row now

    Please Login or Register  to view this content.
    You are an absolute legend mike!

    Thank you so much, just saved me days!

  35. #35
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: VBA for UK Postcodes

    Your welcome, please mark thread closed.

  36. #36
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: VBA for UK Postcodes

    Quote Originally Posted by hannsg View Post
    Sanram, your solution sorts the first postcode AB1 then an error occurs says subscript out of range.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Comparing postcodes
    By silver5040 in forum Excel General
    Replies: 2
    Last Post: 11-17-2014, 05:56 AM
  2. Getting postcodes from a website using VBA
    By jseages in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-19-2014, 06:44 AM
  3. Distance to many UK postcodes
    By samcdavies in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-09-2014, 12:13 PM
  4. [SOLVED] Vlookup Postcodes
    By Tafferel in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 07-04-2012, 06:50 AM
  5. Help with Splitting Postcodes
    By jo1234 in forum Excel General
    Replies: 1
    Last Post: 07-16-2008, 09:46 AM
  6. Counting postcodes
    By mikeyfear in forum Excel General
    Replies: 5
    Last Post: 05-15-2008, 09:41 AM
  7. Postcodes
    By PH NEWS in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-05-2006, 05:15 AM

Tags for this Thread

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