# UK Postcode formula

1. ## 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.

KeLee  Register To Reply

2. ## 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.
>
>
> KeLee  Register To Reply

3. ## 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.
> >
> >
> > KeLee

>  Register To Reply