+ Reply to Thread
Results 1 to 13 of 13

UK Post Code Problem

  1. #1
    Registered User
    Join Date
    11-21-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    19

    UK Post Code Problem

    Hi All

    I'm new to this site, just joined up.

    I have a problem that frustrates me.

    In the UK we have a post code system that is generally in the format say, CF45 9QG as in LLNN NLL, now there are exceptions to the rule in certain part of the country but the worse scenario is LN NLL. (where L = letter & N= number)

    Basically my problem is lazy people who enter post codes in all sorts of formats for example cf459Qg or b19cF (this would be LN NLL as in B1 9CF) into an on line application. The output from the on line application is sent to me in excel as one line for each submission and I cant affect how the information comes to me.

    I then import the information into a worksheet where the information is then sent to the rest of the other sheets in the workbook, so far so good. One of the sheets in the workbook creates an invoice who's number is based on the post code. I can sort the case out with the "upper" function but the problem is the gap between the numbers.

    What I think I need is a formula that puts in the gap for me which is always 3 digitd from the RHS

    I'm open to any suggestions, it seems a shame to have to import the information and then have to go in and check/edit this for every submission.

    Kind regards

    Paul

  2. #2
    Forum Contributor
    Join Date
    09-30-2013
    Location
    Toronto, ON
    MS-Off Ver
    Excel 2007
    Posts
    229

    Re: UK Post Code Proble

    I think this formula does what you want. Assuming your code is in A4.
    Please Login or Register  to view this content.
    If you have some postal codes with the correct format, this formula should be used
    Please Login or Register  to view this content.
    If this helped, please click (*) Add Reputation

  3. #3
    Registered User
    Join Date
    11-21-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    19

    Re: UK Post Code Proble

    Hi

    Many thanks for your reply, that worked first time!

    I've never seen the LEN function before, I'm going to look that up when I'm having a tea break tomorrow!

    Thanks again

    Kind regards

    Paul

  4. #4
    Forum Contributor
    Join Date
    09-30-2013
    Location
    Toronto, ON
    MS-Off Ver
    Excel 2007
    Posts
    229

    Re: UK Post Code Proble

    The LEN function is nothing special.

    What is intricate is this piece (I previously found on the 'net)
    Please Login or Register  to view this content.
    that simulates a function to count the number of characters in a string.

    Why Microsoft wouldn't have a built in function that does this is beyond me.

  5. #5
    Registered User
    Join Date
    11-21-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    19

    Re: UK Post Code Proble

    We all learn from each other, how we managed before the net I don't know

    I have just upgraded to windows 8.1 and office 2013. Microsoft have take quite a few things out that have caused me problems. Why they have to keep changing every few months begers belief.

  6. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: UK Post Code Proble

    This takes the length of the code string and applies a formula according to the length making sure that the last 3 characters are kept together and separated from the rest of the string by a space. This assumes a minimum code length of 5 characters and a maximum of 8 characters.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  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,091

    Re: UK Post Code Proble

    Another option:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    HTML Code: 


    Regards, TMS
    Last edited by TMS; 11-21-2013 at 08:42 PM.
    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
    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,091

    Re: UK Post Code Proble

    Or, a bit neater:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Credit newdoverman for wrapping the whole thing in UPPER instead of the component parts


    Regards, TMS
    Last edited by TMS; 11-21-2013 at 09:03 PM.

  9. #9
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: UK Post Code Proble

    Now, that's a nice formula! Very elegant!

  10. #10
    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,091

    Re: UK Post Code Proble

    @newdoverman: thanks ... you helped make it that bit neater and shorter

  11. #11
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: UK Post Code Proble

    @TMS: you're welcome. I'm glad to have helped a little bit by just stumbling around trying to help.

  12. #12
    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,091

    Re: UK Post Code Proble

    Every little helps ... and we learn as we go

  13. #13
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: UK Post Code Proble

    You can use REPLACE function to shorten further, i.e.

    =UPPER(REPLACE(SUBSTITUTE($A2," ",""),LEN(SUBSTITUTE($A2," ",""))-2,0," "))
    Audere est facere

+ 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] Post Code (Zip Code) Forula
    By SaleenaKhan in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-11-2013, 09:07 AM
  2. Look up originating post code
    By marklister in forum Excel General
    Replies: 8
    Last Post: 02-06-2012, 10:47 AM
  3. Help with Post code format
    By jango in forum Excel General
    Replies: 2
    Last Post: 12-09-2009, 06:19 PM
  4. Where Do I Post Code
    By scotfitz in forum Excel General
    Replies: 1
    Last Post: 04-02-2008, 06:45 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