Seperate Text and numbers in Post code

  • Thread starter Thread starter chriso
  • Start date Start date
C

chriso

Hi
I have a data sheet and need to get just the area from a post code e.g BS1
1JN I need just the BS in a column. I would use left,2 etc but problem is
some codes such as Birmingham only have 1 letter.

Any ideas please.
Thanks
Chris
 
I think the maximum is two letters at the beginning of a postcode, so
you could do this:

=IF(AND(MID(A1,2,1)>="0",MID(A1,2,1)<="9"),LEFT(A1,1),LEFT(A1,2))

assuming the postcode is in A1.

Hope this helps.

Pete
 
Here is another possibility...

=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1)

Rick
 
Back
Top