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

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

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