Postcodes

Hi,

I need a formula that will extract the part of a post code I want. I would
like to extract the first letter or letters from a postcode to use in a
VLOOKUP. My problem is that there can be one letter or two letters at the
start of the postcode.
So if the postcode is B1 1AZ the formula result would be B and if the
formula is BA1 1AZ the result would be BA.

Thanks

The other problem which you haven't mentioned is that the numeric part
could be more than one character long (EG SG15 2ST)

with a postcode in A1, this formula gets to the first part of the code

=LEFT(A1,SEARCH(" ",A1)-1)

however, it doesn't resolve it - SO would suggest using a user defined
function as follows


Function pcodereturn(Postcode As String)
While Left(Postcode, 1) <> " " And (Asc(Left(Postcode, 1)) < 48 Or
Asc(Left(Postcode, 1)) > 57)
pcodereturn = pcodereturn & Left(Postcode, 1)
Postcode = Right(Postcode, Len(Postcode) - 1)
Wend


End Function

I didn't think it matter how many numbers were in the code. All I wanted to
do was extract the letters. I thought there may be a function to say, find
number in range, return position, and then use that number in a LEFT
function? So in your example SG15 2ST excel would return a 3 as that is the
position of the number and then I could use that minus 1 in a LEFT. So it
wouldn't matter if it were SG155454256 2ST, however it would matter if the
code was S155454256 2ST, as I would now need the result 1.
Do you know if there is a symbol or one of the number keys apply with shift
that tells excel just to find any number?
Thanks for your first relply.

SPL
<aidan.heritage@virgin.net> wrote in message
news:1146755508.738795.320390@u72g2000cwu.googlegroups.com...
> The other problem which you haven't mentioned is that the numeric part
> could be more than one character long (EG SG15 2ST)
>
> with a postcode in A1, this formula gets to the first part of the code
>
> =LEFT(A1,SEARCH(" ",A1)-1)
>
> however, it doesn't resolve it - SO would suggest using a user defined
> function as follows
>
>
> Function pcodereturn(Postcode As String)
> While Left(Postcode, 1) <> " " And (Asc(Left(Postcode, 1)) < 48 Or
> Asc(Left(Postcode, 1)) > 57)
> pcodereturn = pcodereturn & Left(Postcode, 1)
> Postcode = Right(Postcode, Len(Postcode) - 1)
> Wend
>
>
> End Function
>

I've just thought of something after I sent the reply, by using the formula
you sent me I always get the first part of the code as it finds the space.
So there are only two combination of the first part letter letter number
number or letter number number. So if you use a LEN to count the characters
if it equals 4 the LEFT function should be two if it equals 3 the LEFT
function should be 1. Right?
Again, thanks.
SPL
<aidan.heritage@virgin.net> wrote in message
news:1146755508.738795.320390@u72g2000cwu.googlegroups.com...
> The other problem which you haven't mentioned is that the numeric part
> could be more than one character long (EG SG15 2ST)
>
> with a postcode in A1, this formula gets to the first part of the code
>
> =LEFT(A1,SEARCH(" ",A1)-1)
>
> however, it doesn't resolve it - SO would suggest using a user defined
> function as follows
>
>
> Function pcodereturn(Postcode As String)
> While Left(Postcode, 1) <> " " And (Asc(Left(Postcode, 1)) < 48 Or
> Asc(Left(Postcode, 1)) > 57)
> pcodereturn = pcodereturn & Left(Postcode, 1)
> Postcode = Right(Postcode, Len(Postcode) - 1)
> Wend
>
>
> End Function
>

Nope that's wrong, you can have letter letter number
"PH NEWS" <steven.lancaster@phresearch.com> wrote in message
news:445a1ffd$0$686$fa0fcedb@news.zen.co.uk...
> I've just thought of something after I sent the reply, by using the
formula
> you sent me I always get the first part of the code as it finds the space.
> So there are only two combination of the first part letter letter number
> number or letter number number. So if you use a LEN to count the
characters
> if it equals 4 the LEFT function should be two if it equals 3 the LEFT
> function should be 1. Right?
> Again, thanks.
> SPL
> <aidan.heritage@virgin.net> wrote in message
> news:1146755508.738795.320390@u72g2000cwu.googlegroups.com...
> > The other problem which you haven't mentioned is that the numeric part
> > could be more than one character long (EG SG15 2ST)
> >
> > with a postcode in A1, this formula gets to the first part of the code
> >
> > =LEFT(A1,SEARCH(" ",A1)-1)
> >
> > however, it doesn't resolve it - SO would suggest using a user defined
> > function as follows
> >
> >
> > Function pcodereturn(Postcode As String)
> > While Left(Postcode, 1) <> " " And (Asc(Left(Postcode, 1)) < 48 Or
> > Asc(Left(Postcode, 1)) > 57)
> > pcodereturn = pcodereturn & Left(Postcode, 1)
> > Postcode = Right(Postcode, Len(Postcode) - 1)
> > Wend
> >
> >
> > End Function
> >
>
>

A little untidy but this works.

Assuming the postcode is in D12, in E12 I have

=IF(ISERROR((VALUE(MID(D12,2,1)))),"10",(VALUE(MID(D12,2,1))))

and in F12 I have

=IF(E12<=9,LEFT(D12,1),LEFT(D12,2))

I'm having trouble putting these two together in the same cell but I think
that might have something to do with it being IF(IF. If anyone could help I
would be very grateful!

"PH NEWS" <steven.lancaster@phresearch.com> wrote in message
news:445a0e84$0$8343$da0feed9@news.zen.co.uk...
> Hi,
>
> I need a formula that will extract the part of a post code I want. I would
> like to extract the first letter or letters from a postcode to use in a
> VLOOKUP. My problem is that there can be one letter or two letters at the
> start of the postcode.
> So if the postcode is B1 1AZ the formula result would be B and if the
> formula is BA1 1AZ the result would be BA.
>
> Thanks
>
>

I have figured out how to have the two in the same cell

=IF(VALUE(IF(ISERROR((VALUE(MID(D12,2,1)))),"10",(VALUE(MID(D12,2,1)))))=10,
LEFT(D12,2),LEFT(D12,1))



"PH NEWS" <steven.lancaster@phresearch.com> wrote in message
news:445a0e84$0$8343$da0feed9@news.zen.co.uk...
> Hi,
>
> I need a formula that will extract the part of a post code I want. I would
> like to extract the first letter or letters from a postcode to use in a
> VLOOKUP. My problem is that there can be one letter or two letters at the
> start of the postcode.
> So if the postcode is B1 1AZ the formula result would be B and if the
> formula is BA1 1AZ the result would be BA.
>
> Thanks
>
>