Home |
Search |
Today's Posts |
#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. Thanks for any help you may provide. KeLee |
#2
|
|||
|
|||
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
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula? | Excel Worksheet Functions | |||
writing a formula for a colored value | New Users to Excel | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Simplify formula | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |