Postcode Area extract first alpha letters

A

aquaxander

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?
 
M

Mike H

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
 
M

Mike H

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
 
J

Jacob Skaria

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
 
J

Jacob Skaria

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Extract all letters from a cell sentence 5
Use Replace function to reduce postcode to postcode region 11
Extract Numbers from Alpha-Numeric String 1
Postcode splitting problem 2
Long List of Numbers sorting 4
More validadtion problems! 50
Extract alpha from a cell 1
Converting postcode to sales area 2

Top