+ Reply to Thread
Results 1 to 8 of 8

Staggered postcodes into one field

  1. #1
    Registered User
    Join Date
    05-13-2018
    Location
    Edinburgh
    MS-Off Ver
    2013
    Posts
    11

    Staggered postcodes into one field

    Hi all,

    Damn these postcodes

    I would be very appreciative if someone could help me out. I've attached a worksheet, it's sample data with a few records, but what I'm trying to do is move all of the postcodes into one column. I can use a filter on the attached example on column I, but in a real world scenario there are too many extra place names to make this a viable option.

    Does anyone know if there is an IF function that can help me separate all of the postcodes into one column, and then a way to be able to delete the postcodes from the original cells after they have been arranged into one column?

    I'd really appreciate some help with this. I'm a complete noob, and this is making up a big part of my new job

    Thanks.
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Staggered postcodes into one field

    Where is this data coming from?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    05-13-2018
    Location
    Edinburgh
    MS-Off Ver
    2013
    Posts
    11
    This data is just a small part of sample data I downloaded, it isn’t actually real info

  4. #4
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: Staggered postcodes into one field

    In the example provided, the LAST cell in the row is always the postcode.
    If you just want to get the value in the LAST CELL, you could use =LOOKUP(2,1/(A2:L2<>""),A2:L2) - place the formula in, say, column M.

    If you subsequently want to delete the PC in the original cells, you have a problem, because your formula will then pick up the next "last cell", so you will lose the PC.

    Thus, if you want to do everything in your post, you will need a VBA macro which does it for you.

    I hope this helps, please let me know!

    Regards,

    David

    If this has been helpful
    - Please click on the *Add Reputation button at the bottom left.

    Please mark your thread as SOLVED:
    - Click Thread Tools above your first post, select "Mark your thread as Solved".


  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Staggered postcodes into one field

    David, brilliant catch!!

  6. #6
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: Staggered postcodes into one field

    Thanks Ford - for the kind comment and the rep.

    DAC

  7. #7
    Registered User
    Join Date
    05-13-2018
    Location
    Edinburgh
    MS-Off Ver
    2013
    Posts
    11
    Thanks for the advice David, it sounds like it will help me a lot.

    Say if after I have my postcodes where I want them, and I copy and paste them as values, so they’re not linked to the original cells, would there be a way to delete the original postcodes then?

  8. #8
    Registered User
    Join Date
    05-13-2018
    Location
    Edinburgh
    MS-Off Ver
    2013
    Posts
    11

    Re: Staggered postcodes into one field

    Hi David. I just got into work and tried this, it works great, thank you. My work around for now will be to copy and paste the data into a new sheet. I don't have to include the last column of the address most of the time because counties aren't required, so I can use this as a work around until I work out the VBA macro.

    Thanks again for your help

+ 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. [SOLVED] staggered email send
    By okmijn in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-03-2021, 12:15 AM
  2. one formula for staggered results
    By stephme55 in forum Excel General
    Replies: 2
    Last Post: 12-17-2015, 01:44 PM
  3. Staggered sum product formula
    By IronCladRooster in forum Excel General
    Replies: 3
    Last Post: 03-05-2015, 05:25 AM
  4. [SOLVED] Staggered/Split For Loops
    By NatalieEC in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-03-2015, 10:21 AM
  5. Staggered rent formula
    By macy liew in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-17-2014, 10:29 AM
  6. staggered formulas
    By amartino44 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-13-2013, 12:44 PM
  7. Add row to table and reformat the staggered coloring
    By aloha31 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-05-2013, 05:53 PM

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