If formula and extracting letters from postcodes

[nathan663]

I am trying to extract the first 2 letters off a postcode

=Left("Cell",2) this works fine if the post code is 2 letters followed by a number eg. JE3...
However if the second digit in the postcode is a number eg. W1 i need to only take the first letter (the W) I have tried using an if formula but can not get this to work... what is the best way?

 

[Expiry]

If your options are only this format AB10 4TP and W1 4TP, then you could use len with left. Something like

=left("cell",len("cell")-6)

Len gives you the length of the string, so if you have 8 characters (including space) or 7 it will always return 2 or 1 characters, respectively.

 

[ukmikeb]

Hi

Try :-

=IF(ISNUMBER(VALUE(MID(R30,2,1))),LEFT(R30,1),LEFT(R30,2))

where R30 is "your" cell.

NB Expiry - yours wouldn't work with "W1A 4WW" which is a valid code too.

hth

Mike

 

[ukmikeb]

Or a neater version of my previous post :-

=LEFT(R30,2---(ISNUMBER(VALUE(MID(R30,2,1)))))

hth

Mike