Excel & GB Postcodes 1

[Acydman]

Has anyone got or written a routine to extract just the postcode sector (i.e. the B in B76 1ar, or the BN in BN7 2re).

Thanks

Mark Ormiston

 

[hopelessliar]

you can do this using the text functions (left and length) in combination with a couple of IF statements. If you are unfamiliar with functions, write back and I'll be more explicit!

 

[Acydman]

I'm not that familiar so a little help would be great.


Thanks

Mark

 

[xlbo]

just need to test backwards from the 2nd character as to whether it is numeric or not...

=IF(AND(ISERROR(VALUE(MID(A7,2,1))),MID(A7,2,1)<>" "),LEFT(A7,2),LEFT(A7,1))

should do the trick

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[Acydman]

It worked a treat

thanks for you rhelp

Mark

 

[gizmo1973]

=IF(ISERROR(VALUE(MID(B25,2,1))),LEFT(B25,2),LEFT(B25,1))

Similar but this is the one I use, can't remeber who gave me this but I won't claim credit, might have even been Geoff

Regards, Phil.

Full Member of Shareholders United.
Show your true support here:

http://www.shareholdersunited.org/join.php

"M.U.F.C. Not for sale to Glazer"

 

[GlennUK]

You can do it by performing a little arithmetic on the code value of the second letter too:

=LEFT(B2,INT(CODE(MID(B2,2,1))/65)+1)



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[KenWright]

You might want to check out the following thread where byundt posted a neat function using Reg Exp.

thread68-890890

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------