+ Reply to Thread
Results 1 to 3 of 3

UK Postcode formula

  1. #1
    KeLee
    Guest

    UK Postcode formula

    Hello lovely people!

    I am working with UK postcodes and need to strip out the areas from them
    automatically.

    My original codes are in column A and the result will be in, say, column B.

    UK postcodes come in one of the following 4 formats - substitute any letters
    or numbers, but the syntax is correct
    B1 5ZQ Letter|Number|Space|Number|Letter|Letter (LNSNLL)
    SL4 5AR (LLNSNLL)
    CV99 4EB (LLNNSNLL)
    EC1V 4AR (LLNLSNLL)

    The areas I want to return for for the above would be:
    B1
    SL4
    CV99
    EC1V

    I am currently using this nested if, which works fine:
    =IF(ISNUMBER(VALUE(MID(A1,4,1))),IF(ISNUMBER(VALUE(MID(A1,3,1))),LEFT(A1,4),LEFT(A1,2)),IF(ISNUMBER(VALUE(MID(A1,3,1))),LEFT(A1,4),LEFT(A1,3)))

    I was wondering if there was a more elegant solution that uses the position
    of the space in some way, as people have difficulty understanding the formula
    above.

    Thanks for any help you may provide.

    KeLee

  2. #2
    Roger Govier
    Guest

    Re: UK Postcode formula

    Hi

    Try
    =LEFT(A1,FIND(" ",A1)-1)

    Regards

    Roger Govier


    KeLee wrote:
    > Hello lovely people!
    >
    > I am working with UK postcodes and need to strip out the areas from them
    > automatically.
    >
    > My original codes are in column A and the result will be in, say, column B.
    >
    > UK postcodes come in one of the following 4 formats - substitute any letters
    > or numbers, but the syntax is correct
    > B1 5ZQ Letter|Number|Space|Number|Letter|Letter (LNSNLL)
    > SL4 5AR (LLNSNLL)
    > CV99 4EB (LLNNSNLL)
    > EC1V 4AR (LLNLSNLL)
    >
    > The areas I want to return for for the above would be:
    > B1
    > SL4
    > CV99
    > EC1V
    >
    > I am currently using this nested if, which works fine:
    > =IF(ISNUMBER(VALUE(MID(A1,4,1))),IF(ISNUMBER(VALUE(MID(A1,3,1))),LEFT(A1,4),LEFT(A1,2)),IF(ISNUMBER(VALUE(MID(A1,3,1))),LEFT(A1,4),LEFT(A1,3)))
    >
    > I was wondering if there was a more elegant solution that uses the position
    > of the space in some way, as people have difficulty understanding the formula
    > above.
    >
    > Thanks for any help you may provide.
    >
    > KeLee


  3. #3
    KeLee
    Guest

    Re: UK Postcode formula

    Thankyou that is beautiful in its simplicity.
    KeLee

    "Roger Govier" wrote:

    > Hi
    >
    > Try
    > =LEFT(A1,FIND(" ",A1)-1)
    >
    > Regards
    >
    > Roger Govier
    >
    >
    > KeLee wrote:
    > > Hello lovely people!
    > >
    > > I am working with UK postcodes and need to strip out the areas from them
    > > automatically.
    > >
    > > My original codes are in column A and the result will be in, say, column B.
    > >
    > > UK postcodes come in one of the following 4 formats - substitute any letters
    > > or numbers, but the syntax is correct
    > > B1 5ZQ Letter|Number|Space|Number|Letter|Letter (LNSNLL)
    > > SL4 5AR (LLNSNLL)
    > > CV99 4EB (LLNNSNLL)
    > > EC1V 4AR (LLNLSNLL)
    > >
    > > The areas I want to return for for the above would be:
    > > B1
    > > SL4
    > > CV99
    > > EC1V
    > >
    > > I am currently using this nested if, which works fine:
    > > =IF(ISNUMBER(VALUE(MID(A1,4,1))),IF(ISNUMBER(VALUE(MID(A1,3,1))),LEFT(A1,4),LEFT(A1,2)),IF(ISNUMBER(VALUE(MID(A1,3,1))),LEFT(A1,4),LEFT(A1,3)))
    > >
    > > I was wondering if there was a more elegant solution that uses the position
    > > of the space in some way, as people have difficulty understanding the formula
    > > above.
    > >
    > > Thanks for any help you may provide.
    > >
    > > KeLee

    >


+ 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