+ Reply to Thread
Results 1 to 17 of 17

Cut UK postcode into separate field

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

    Cut UK postcode into separate field

    Hi. I have a problem with cutting/trimming UK postcodes from the end of an address into their own column. I have found a few solutions online that allows me to extract postcodes into their own column, but the issue I'm having is that the postcode remains at the end of the address. I need to parse the remaining addresses into their own columns, but the postcode must all be under the same column. Example:

    1 Street View, Town, Area, County, ER2 5AW
    2 Avenue Way, Town, County, STW 5B
    3 High Close, Town, TU 5RW

    As you can see the addresses and postcodes are irregular, and I have found a solution to move the postcode to it's own column, but I need it to cut from the end of the address. The solution to move the postcode is as follows:

    =RIGHT(SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1),LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1)))

    Does anyone know how I can amend this formula to CUT the postcode away from the address into it's own field. Any help would very much be appreciated. Thank you.

  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: Cut UK postcode into separate field

    Hi, welcome to the forum

    Formulas can only affect the cell they reside in, they do not work on any other cell.

    You will need to create another helper column and (probably) use Find/Replace to remove the PC into the new column
    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

    Re: Cut UK postcode into separate field

    Of course, I should have realised the formula doesn't effect the column it's referencing. Thank you for this tip. Will a find and replace be able to pick up irregular postcodes? Sorry, I'm quite new to excel, and I need to figure this out as it makes up a large part of my new job.

  4. #4
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

    Re: Cut UK postcode into separate field

    most of use won't be able too simple because we do not live in the UK and have no idea how a regular postcode looks like. for me a "regular postcode" looks like "1234 AB" and I'm sure that is not what a UK one looks like

    So in order to help you, the easiest way would be to make an example file with test data
    and tell us what a regular postcode looks like so we can test for "irregularities"

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

    Re: Cut UK postcode into separate field

    Thanks for your reply. I've attached an image explaining what I'm trying to do. There are only 4 records in my example, but this is quite typical of the type of data that I work with on a daily basis.

    Thanks.Example.JPG

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Cut UK postcode into separate field

    Attach a sample workbook (not image) as we cannot work with images nor are we going to type in your data.

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Cut UK postcode into separate field

    Assuming you extract the post code from cell A2 into cell B2, you could use this in cell C2 (for example) to remove the post code:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    or, to lose the last comma, use:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


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

    Re: Cut UK postcode into separate field

    Sorry, I did want to upload the workbook but wasn't sure how, thanks for telling me. The data should be attached now.
    Attached Files Attached Files

  9. #9
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Cut UK postcode into separate field

    With PowerQuery
    Attached Files Attached Files

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

    Re: Cut UK postcode into separate field

    Thank you TMS. The first formula did produce what I need, although with the comma. I'm not sure why, but the second formula produced results where the postcode was still at the end.

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

    Re: Cut UK postcode into separate field

    Thanks Sandy. How to you achieve this? I have 11,000 records to get done first thing tomorrow morning, and it would be great to know how you separated the postcode out from the address.

  12. #12
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Cut UK postcode into separate field

    You can use Power Query for
    and split column by right most delimiter (comma in this case)

  13. #13
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Cut UK postcode into separate field

    In B2

    =SUBSTITUTE(A2,", " &C2,"")

    to remove the comma

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

    Re: Cut UK postcode into separate field

    Thank you very much John, this is a big help. And thanks to all who contributed.

  15. #15
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Cut UK postcode into separate field

    If that takes care of your original question, & to say Thanks, please
    1. click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
      then
    2. select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
    If you did it already - ignore it.
    Thank you.

  16. #16
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Cut UK postcode into separate field

    You're welcome. Thanks for the rep.

  17. #17
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Cut UK postcode into separate field

    Sorry, let's try that again

    Assuming you extract the post code from cell A2 into cell B2, you could use this in cell C2 (for example) to remove the post code:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    or, to lose the last comma, use:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ 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. Remove PostCode from Address field
    By aedislee in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-20-2014, 09:53 AM
  2. Postcode to postcode distance checker
    By KerryLL1221 in forum Excel General
    Replies: 1
    Last Post: 02-21-2014, 09:42 AM
  3. Lookup postcode zone from postcode
    By Elmholt in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-08-2013, 09:26 AM
  4. [SOLVED] Help needed trimming full postcode address to postcode sector.
    By Mikey7346 in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 08-10-2012, 05:11 PM
  5. Help with calculating VAT in a separate field
    By elyone in forum Excel General
    Replies: 2
    Last Post: 07-17-2011, 07:11 AM
  6. How to parse a name field and separate First, Mid, Last
    By harrywaldron in forum Excel General
    Replies: 1
    Last Post: 07-26-2007, 10:51 AM
  7. How do I separate data in address field?
    By Baylynx in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 04-28-2006, 04:40 PM

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