Further Trimming postcode issue

[RICK150]

Hello There, just wondering if you guys mind helping me out once again with yet another small postcode issue.

I'll try to explain:-

In column A i have a list of UK postcodes. The problem is i just want the letters in the first half of the postcode. and the postcodes vary. So

LE67 2QN should return LE
B62 1AA should return B
E1W 1AA should return E

I've managed to get the previous query working about splitting it at the space but now would like to spilt one further too and am struggling.

If anyone could help me at all i'd really appreciate it.

Thanks and if you need any clarity let me know.

Ricky

 

[barry houdini]

If your postcode is in A1 use this formula to return either first letter or first 2 letters

=LEFT(A1,1+ISERR(MID(A1,2,1)+0))

 

[RICK150]

I'm afraid to say this hasn't worked.

It works great for postcodes with two letters EG :-

'LE65 1AA' does return 'LE'
'DE39 1AA' does return 'DE'
'W1 1AA' RETURNS 'W1' not 'W'

you can't work it off the space in the postcode. I need something that detects when number start in string then i can left the result of that to limit it just to numbers.

Unless that is anyone else has any other ideas that will work i'm perfectly willing to listen to other suggestions with thanks.

Many thanks in advance for any help anybody can give me.

THANK YOU

RICKY

 

[pgc01]

Hi Ricky

I just tried Barry's formula and it works for me.

'W1 1AA' returns 'W', as you want.

Check the last part of the formula. Maybe by accident you typed +O (letter O) instead of +0 (number zero)?

Hope this helps
PGC

 

[RICK150]

AHA how stupid do I feel now My apologies for this its working fine, and would have done first time if I could type!!?!?

Thank you all for your help and patiaence with me.

THANK YOU THANK YOU THANK YOU

Ricky (Far from worthy)