software

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(quot; quot;,A1)-1)

however, it doesn't resolve it - SO would suggest using a user defined
function as followsFunction pcodereturn(Postcode As String)
While Left(Postcode, 1) lt;gt; quot; quot; And (Asc(Left(Postcode, 1)) lt; 48 Or
Asc(Left(Postcode, 1)) gt; 57)
pcodereturn = pcodereturn amp; Left(Postcode, 1)
Postcode = Right(Postcode, Len(Postcode) - 1)
WendEnd FunctionI 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
gt; wrote in message oups.com...
gt; The other problem which you haven't mentioned is that the numeric part
gt; could be more than one character long (EG SG15 2ST)
gt;
gt; with a postcode in A1, this formula gets to the first part of the code
gt;
gt; =LEFT(A1,SEARCH(quot; quot;,A1)-1)
gt;
gt; however, it doesn't resolve it - SO would suggest using a user defined
gt; function as follows
gt;
gt;
gt; Function pcodereturn(Postcode As String)
gt; While Left(Postcode, 1) lt;gt; quot; quot; And (Asc(Left(Postcode, 1)) lt; 48 Or
gt; Asc(Left(Postcode, 1)) gt; 57)
gt; pcodereturn = pcodereturn amp; Left(Postcode, 1)
gt; Postcode = Right(Postcode, Len(Postcode) - 1)
gt; Wend
gt;
gt;
gt; End Function
gt;
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
gt; wrote in message oups.com...
gt; The other problem which you haven't mentioned is that the numeric part
gt; could be more than one character long (EG SG15 2ST)
gt;
gt; with a postcode in A1, this formula gets to the first part of the code
gt;
gt; =LEFT(A1,SEARCH(quot; quot;,A1)-1)
gt;
gt; however, it doesn't resolve it - SO would suggest using a user defined
gt; function as follows
gt;
gt;
gt; Function pcodereturn(Postcode As String)
gt; While Left(Postcode, 1) lt;gt; quot; quot; And (Asc(Left(Postcode, 1)) lt; 48 Or
gt; Asc(Left(Postcode, 1)) gt; 57)
gt; pcodereturn = pcodereturn amp; Left(Postcode, 1)
gt; Postcode = Right(Postcode, Len(Postcode) - 1)
gt; Wend
gt;
gt;
gt; End Function
gt;
Nope that's wrong, you can have letter letter number
quot;PH NEWSquot; gt; wrote in message
...
gt; I've just thought of something after I sent the reply, by using the
formula
gt; you sent me I always get the first part of the code as it finds the space.
gt; So there are only two combination of the first part letter letter number
gt; number or letter number number. So if you use a LEN to count the
characters
gt; if it equals 4 the LEFT function should be two if it equals 3 the LEFT
gt; function should be 1. Right?
gt; Again, thanks.
gt; SPL
gt; gt; wrote in message
gt; oups.com...
gt; gt; The other problem which you haven't mentioned is that the numeric part
gt; gt; could be more than one character long (EG SG15 2ST)
gt; gt;
gt; gt; with a postcode in A1, this formula gets to the first part of the code
gt; gt;
gt; gt; =LEFT(A1,SEARCH(quot; quot;,A1)-1)
gt; gt;
gt; gt; however, it doesn't resolve it - SO would suggest using a user defined
gt; gt; function as follows
gt; gt;
gt; gt;
gt; gt; Function pcodereturn(Postcode As String)
gt; gt; While Left(Postcode, 1) lt;gt; quot; quot; And (Asc(Left(Postcode, 1)) lt; 48 Or
gt; gt; Asc(Left(Postcode, 1)) gt; 57)
gt; gt; pcodereturn = pcodereturn amp; Left(Postcode, 1)
gt; gt; Postcode = Right(Postcode, Len(Postcode) - 1)
gt; gt; Wend
gt; gt;
gt; gt;
gt; gt; End Function
gt; gt;
gt;
gt;
A little untidy but this works.

Assuming the postcode is in D12, in E12 I have

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

and in F12 I have

=IF(E12lt;=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!

quot;PH NEWSquot; gt; wrote in message
...
gt; Hi,
gt;
gt; I need a formula that will extract the part of a post code I want. I would
gt; like to extract the first letter or letters from a postcode to use in a
gt; VLOOKUP. My problem is that there can be one letter or two letters at the
gt; start of the postcode.
gt; So if the postcode is B1 1AZ the formula result would be B and if the
gt; formula is BA1 1AZ the result would be BA.
gt;
gt; Thanks
gt;
gt;
I have figured out how to have the two in the same cell

=IF(VALUE(IF(ISERROR((VALUE(MID(D12,2,1)))),quot;10quot;,( VALUE(MID(D12,2,1)))))=10,
LEFT(D12,2),LEFT(D12,1))
quot;PH NEWSquot; gt; wrote in message
...
gt; Hi,
gt;
gt; I need a formula that will extract the part of a post code I want. I would
gt; like to extract the first letter or letters from a postcode to use in a
gt; VLOOKUP. My problem is that there can be one letter or two letters at the
gt; start of the postcode.
gt; So if the postcode is B1 1AZ the formula result would be B and if the
gt; formula is BA1 1AZ the result would be BA.
gt;
gt; Thanks
gt;
gt;