PostCode Extraction data

[Bravo2003]

Hi All,
This should be a simple one?

I'm currently using - =IF(D3="EXC","",LEFT(D3,2))

Column D being a list of postcodes.
works fine for most Postcodes, but the single letter ones - i.e Sheffield/S and Birmingham/B it add the number to it, obviously as i'm asking for the 2 characters in the formula.

Is there a way to make it not include numbers and just return either the 2 letter or 1 letter Postcode required?

Thanks

PS: - I actually reduced the formula to just - =LEFT(D3,2) as I don't need the IF part anymore.

 

[tyija1995]

Hey try this:

IF(ISNUMBER(SEARCH(MID(D3,2,1),"0123456789")),LEFT(D3,1),LEFT(D3,2))

Or slightly shorter:

IF(ISNUMBER(VALUE(MID(D3,2,1))),LEFT(D3,1),LEFT(D3,2))

 

[Bravo2003]

Hey try this:

IF(ISNUMBER(SEARCH(MID(D3,2,1),"0123456789")),LEFT(D3,1),LEFT(D3,2))

Or slightly shorter:

IF(ISNUMBER(VALUE(MID(D3,2,1))),LEFT(D3,1),LEFT(D3,2))

Excellent, Thanks
Knew it would be a simple one.

 

[jmacleary]

Or this:

=IF(ISNUMBER(--MID(D3,2,1)),LEFT(D3,1),LEFT(D3,2))