Hello all,

Am a bit stuck here, probably partly because its a friday afternoon...

Basically i am making a price calculator which works out how much it will
cost to deliver to a postcode input by the user. There are 3 different price
zones, and I have a list of which price zones go with which postal areas and
districts.

(NB: A UK postcode consists of the following format: CV22 2JF. the "CV" is
the postal area, "cv21" is the postal district)


The problem is that not all the postal areas are completely in one zone, so
we have to look at the postal district - ie. instead of just looking for "PE"
postal area it needs to look for PE1 or PE17 etc. Thats where the problem
comes in.

I have already spent ages working out how to make it find only the leading
letters at the start by testing each letter consecutively to see if its a
number with the code:

=IF(ISNUMBER(VALUE(MID(D5,2,1))),LEFT(D5,1),IF(ISN UMBER(VALUE(MID(D5,3,1))),LEFT(D5,2),IF(ISNUMBER(V ALUE(MID(D5,3,1))),LEFT(D5,3),LEFT(D5,4))))


That works ok, another cell now looks at whether its an N/A error or not,
and if it is tries including the some numbers as well:

=IF(ISNA(IF(ISNA(VLOOKUP(D13,'Zones
data'!A1:C610,3,0)),VLOOKUP(LEFT(D13,2),'Zones
data'!A1:C608,2,0),VLOOKUP(D13,'Zones
data'!A1:C543,2,0))),IF(ISNUMBER(VALUE(MID(postcod e,4,1))),VLOOKUP(LEFT(postcode,4),'Zones
data'!A1:C359,2,0),VLOOKUP(LEFT(postcode,3),'Zones
data'!A1:C293,2,0)),IF(ISNA(VLOOKUP(D13,'Zones
data'!A1:C610,3,0)),VLOOKUP(LEFT(D13,2),'Zones
data'!A1:C608,2,0),VLOOKUP(D13,'Zones data'!A1:C543,2,0)))

Thing is, its not working on the postcodes which should be using the postal
district because that is a split area. e.g. the postcode PE17 xyz should be
identified as a zone two according to the table of zones data:

Postcode start Zone
....
CV 3
PE14 1
PE15 1
PE16 1
PE17 2
....etc...

But anything that requires numbers as part of the search is turning up a N/A
error and I am too tired to follow my own formula now and work out where i am
going wrong.

Please help with possibly the longest question of all time!

Thanks,
Oli