UK Postcode formula

[Guest]

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

 

[Roger Govier]

Hi

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

Regards

Roger Govier

 

[Guest]

Thankyou that is beautiful in its simplicity.
KeLee