Postcode Area extract first alpha letters

Hi,
I have a list of postcodes and would like to have only the first letters,
whether this is 2 or 1 letter.
I have been able to extract the beginning of a postcode with
=LEFT(A1,FIND(" ",A1)-1)
but now want to split it further and just have the letters not the numbers.
Eg:
"B1" return "B"
"SW12" return "SW"
"M23" return "M"
"NN9" return "NN" etc.
is there something that extracts alpha characters?

Hi,

It would have helped to see a full code but I assume these are the first 1
or 2 alpha characters you want to extract. Try this

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

Mike

"aquaxander" wrote:

Hi,
I have a list of postcodes and would like to have only the first letters,
whether this is 2 or 1 letter.
I have been able to extract the beginning of a postcode with
=LEFT(A1,FIND(" ",A1)-1)
but now want to split it further and just have the letters not the numbers.
Eg:
"B1" return "B"
"SW12" return "SW"
"M23" return "M"
"NN9" return "NN" etc.
is there something that extracts alpha characters?

Hi,

Try this =LEFT(C14,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C14&"01 23456789"))-1)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"aquaxander" wrote in message
...
Hi,
I have a list of postcodes and would like to have only the first letters,
whether this is 2 or 1 letter.
I have been able to extract the beginning of a postcode with
=LEFT(A1,FIND(" ",A1)-1)
but now want to split it further and just have the letters not the
numbers.
Eg:
"B1" return "B"
"SW12" return "SW"
"M23" return "M"
"NN9" return "NN" etc.
is there something that extracts alpha characters?

Assuming the entries will have only 1 or 2 alphas in front..you can try this

=IF(ISERROR(1*MID(A1,2,1)),LEFT(A1,2),LEFT(A1,1))

If this post helps click Yes
---------------
Jacob Skaria


"aquaxander" wrote:

Hi,
I have a list of postcodes and would like to have only the first letters,
whether this is 2 or 1 letter.
I have been able to extract the beginning of a postcode with
=LEFT(A1,FIND(" ",A1)-1)
but now want to split it further and just have the letters not the numbers.
Eg:
"B1" return "B"
"SW12" return "SW"
"M23" return "M"
"NN9" return "NN" etc.
is there something that extracts alpha characters?