Novice when it comes to using If statements and Vlookup.

I have a list of people and one cell containing the full address (street,
town, postcode). I want to be able to sort all the records into districts.
I can produce a list of which towns are in which districts which would e good
in a vlookup table but how do I extract the town from the cell.

Is there a method to tell the lookup table to match part of the cell. Or do
I have to extract the town from cell into a separate column that can then be
used for the Vlookup? If so, how?

Towns and postcodes are irregular in length. So can't count characters from
right as I have seen in others samples on this site.

Any help would be most gratefully appreciated.

Tell us more with two or three examples of the table data and the data that
forms the first argument in the lookup
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme


"LadyHawk" wrote in message
...
Novice when it comes to using If statements and Vlookup.

I have a list of people and one cell containing the full address (street,
town, postcode). I want to be able to sort all the records into
districts.
I can produce a list of which towns are in which districts which would e
good
in a vlookup table but how do I extract the town from the cell.

Is there a method to tell the lookup table to match part of the cell. Or
do
I have to extract the town from cell into a separate column that can then
be
used for the Vlookup? If so, how?

Towns and postcodes are irregular in length. So can't count characters
from
right as I have seen in others samples on this site.

Any help would be most gratefully appreciated.

I have a list of addresses in column A for example e.g: (fictitious addresses)
8 cherry close, Exmouth, Devon, EX8 5PR
Drake cottage, 39 North Road, Exeter, Devon, PL1 4TU
6 South Street, Dawlish, Devon
4 cowick street, Broadclyst, Exeter, Devon,

In looking at the data more closely today, I have spotted that I have
additional problems that the data is not uniform, some addresses missed the
post code and some addresses include a building name e.g. Drake cottage, as
well as a street address. I am recommending a validation process be
undertaken to sort this so that all address are full and include the post
code.

I have been reading examples on the net that teach how to split full names

http://www.cpearson.com/excel/FirstLast.htm

I found this interesting and understood it but how would I get around the
few addresses that have building names. I would not be able to say that
everything left of the first comma is street, if there are a few where that
is a building name instead.

The information I need to extract is the name of the town as I can produce a
list of all towns in the "districts" we cover, but would also need to have an
option for if not on that list mark it is "out of our area". Thus I would
like to construct a Vlookup table to include for instance:

Broadclyst Mid Devon
Dawlish Mid Devon
Exeter Exeter
Exmouth East Devon
Sidmouth East Devon


Just to get even more problematic, Exeter being a city has a couple of areas
on the border that fall into a different district even though still within
Exeter e.g. Broadclyst, Exeter. The broadclyst part of Exeter is one
District while Exeter is a separate District. I would want the data to read
the first part of the address and ignore the second even if that is also a
entry on my vlookup table.

Does this make any sense. Sadly the data was input into a database and the
information extracted using a report that I do not control. I feel that the
address was probably linked together into one field for the purpose of the
report but for this task, that is clearly not ideal.

"Bernard Liengme" wrote:

Tell us more with two or three examples of the table data and the data that
forms the first argument in the lookup
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme


"LadyHawk" wrote in message
...
Novice when it comes to using If statements and Vlookup.

I have a list of people and one cell containing the full address (street,
town, postcode). I want to be able to sort all the records into
districts.
I can produce a list of which towns are in which districts which would e
good
in a vlookup table but how do I extract the town from the cell.

Is there a method to tell the lookup table to match part of the cell. Or
do
I have to extract the town from cell into a separate column that can then
be
used for the Vlookup? If so, how?

Towns and postcodes are irregular in length. So can't count characters
from
right as I have seen in others samples on this site.

Any help would be most gratefully appreciated.