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?

Answer: Postcode Area extract first alpha letters
 

1. Code:

   ---

   =LEFT(A1,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1),LEN(A1)+1))-1)

   ---

2. Enter the formula in a blank cell next to the postcode list.
3. Replace "A1" in the formula with the cell reference of the postcode you want to extract the alpha characters from.
4. Press Enter to apply the formula to the cell.
5. Copy the formula down to apply it to the rest of the postcode list.

Postcode Area extract first alpha letters
 

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?

Postcode Area extract first alpha letters
 

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?

Postcode Area extract first alpha letters
 

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?