Hi,

I would but i cant guareentee that the space will be in there, maybe i will
just validate it to make the user have to put the space in.

Out of interest anyone know what wud need to do if i wanted to do this
without the space?

"Roger Govier" wrote:

Hi Oli

to get the Postal District just use
=LEFT(A1,FIND(" ",A1)-1)

To get the Zone use
=MID(A1,FIND(" ",A1)+1,1)

--
Regards

Roger Govier


"oli merge" wrote in message
...
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