Hi there,
I work in the mobile industry running campaigns and so forth. We received
the sms entry report in a excel spreadsheet.
What I need to do is find the region of entrants. The Entry Data report has
the date, Cell number and text which was entered across columns. The Text
that they have entered will have their City or region in it eg. "Hi I live in
Cape Town"
We'll say that his text is in A2:A100
What I did was, I created a table of Cities of South Africa and in which
province they are in.
We'll say the the City is on Sheet [City] A2:A100 and the Province is on
Sheet [City] B2:B100.
I need to match the name of the city found in the entry text to the name of
the city in the table that I have created and the result should be the
province.
Can this be done ?

A regular VLOOKUP formula will do to locate the city in the list and return
the Province.....that part is easy. First however, you will have to break
out the City name from the text and have it in it's own cell/column. Excel
cannot look at a string of text directly and determine which parts of it
might be considered a city name.

hth
Vaya con Dios,
Chuck, CABGx3



"Roseygains" wrote:

Hi there,
I work in the mobile industry running campaigns and so forth. We received
the sms entry report in a excel spreadsheet.
What I need to do is find the region of entrants. The Entry Data report has
the date, Cell number and text which was entered across columns. The Text
that they have entered will have their City or region in it eg. "Hi I live in
Cape Town"
We'll say that his text is in A2:A100
What I did was, I created a table of Cities of South Africa and in which
province they are in.
We'll say the the City is on Sheet [City] A2:A100 and the Province is on
Sheet [City] B2:B100.
I need to match the name of the city found in the entry text to the name of
the city in the table that I have created and the result should be the
province.
Can this be done ?

Thanks CLR, unfortanelty I cannot extract the City or region names out of
entry text because they are not in a constant position and also this is a
rather big file which would then just take forever.

So you saying that there is not way I can combine the if, vlookup, match,
search or indext formula.

"CLR" wrote:

A regular VLOOKUP formula will do to locate the city in the list and return
the Province.....that part is easy. First however, you will have to break
out the City name from the text and have it in it's own cell/column. Excel
cannot look at a string of text directly and determine which parts of it
might be considered a city name.

hth
Vaya con Dios,
Chuck, CABGx3



"Roseygains" wrote:

Hi there,
I work in the mobile industry running campaigns and so forth. We received
the sms entry report in a excel spreadsheet.
What I need to do is find the region of entrants. The Entry Data report has
the date, Cell number and text which was entered across columns. The Text
that they have entered will have their City or region in it eg. "Hi I live in
Cape Town"
We'll say that his text is in A2:A100
What I did was, I created a table of Cities of South Africa and in which
province they are in.
We'll say the the City is on Sheet [City] A2:A100 and the Province is on
Sheet [City] B2:B100.
I need to match the name of the city found in the entry text to the name of
the city in the table that I have created and the result should be the
province.
Can this be done ?

No, I did not say that. I said that Excel cannot by itself determine which
portions of a string are to be considered a city name.

You said, "I cannot extract the City or region names out of
entry text because they are not in a constant position ". That may not
necessarily be true. If there are any consistant conditions surrounding the
city name, such as leading or trailing unique punctuation marks, numbers,
multiple spaces, etc....it may be possible to automatically extract the city
names.

If you can identify some commonality, post a few extreme examples, but do
not attach a file here.


Vaya con Dios,
Chuck, CABGx3



"Roseygains" wrote:

Thanks CLR, unfortanelty I cannot extract the City or region names out of
entry text because they are not in a constant position and also this is a
rather big file which would then just take forever.

So you saying that there is not way I can combine the if, vlookup, match,
search or indext formula.

"CLR" wrote:

A regular VLOOKUP formula will do to locate the city in the list and return
the Province.....that part is easy. First however, you will have to break
out the City name from the text and have it in it's own cell/column. Excel
cannot look at a string of text directly and determine which parts of it
might be considered a city name.

hth
Vaya con Dios,
Chuck, CABGx3



"Roseygains" wrote:

Hi there,
I work in the mobile industry running campaigns and so forth. We received
the sms entry report in a excel spreadsheet.
What I need to do is find the region of entrants. The Entry Data report has
the date, Cell number and text which was entered across columns. The Text
that they have entered will have their City or region in it eg. "Hi I live in
Cape Town"
We'll say that his text is in A2:A100
What I did was, I created a table of Cities of South Africa and in which
province they are in.
We'll say the the City is on Sheet [City] A2:A100 and the Province is on
Sheet [City] B2:B100.
I need to match the name of the city found in the entry text to the name of
the city in the table that I have created and the result should be the
province.
Can this be done ?

780Ka Bethal Gert Sibande District
194Vg My Name Is Richard Ngema From Standerton
Peter Mcrae Kemppark 07XXXXXXXX 113Xx


"CLR" wrote:

No, I did not say that. I said that Excel cannot by itself determine which
portions of a string are to be considered a city name.

You said, "I cannot extract the City or region names out of
entry text because they are not in a constant position ". That may not
necessarily be true. If there are any consistant conditions surrounding the
city name, such as leading or trailing unique punctuation marks, numbers,
multiple spaces, etc....it may be possible to automatically extract the city
names.

If you can identify some commonality, post a few extreme examples, but do
not attach a file here.


Vaya con Dios,
Chuck, CABGx3



"Roseygains" wrote:

Thanks CLR, unfortanelty I cannot extract the City or region names out of
entry text because they are not in a constant position and also this is a
rather big file which would then just take forever.

So you saying that there is not way I can combine the if, vlookup, match,
search or indext formula.

"CLR" wrote:

A regular VLOOKUP formula will do to locate the city in the list and return
the Province.....that part is easy. First however, you will have to break
out the City name from the text and have it in it's own cell/column. Excel
cannot look at a string of text directly and determine which parts of it
might be considered a city name.

hth
Vaya con Dios,
Chuck, CABGx3



"Roseygains" wrote:

Hi there,
I work in the mobile industry running campaigns and so forth. We received
the sms entry report in a excel spreadsheet.
What I need to do is find the region of entrants. The Entry Data report has
the date, Cell number and text which was entered across columns. The Text
that they have entered will have their City or region in it eg. "Hi I live in
Cape Town"
We'll say that his text is in A2:A100
What I did was, I created a table of Cities of South Africa and in which
province they are in.
We'll say the the City is on Sheet [City] A2:A100 and the Province is on
Sheet [City] B2:B100.
I need to match the name of the city found in the entry text to the name of
the city in the table that I have created and the result should be the
province.
Can this be done ?

780Ka Bethal Gert Sibande District
194Vg My Name Is Richard Ngema From Standerton
Peter Mcrae Kemppark 07XXXXXXXX 113Xx

I have the separate spreadsheet listing the name of the cities in cell A2
and the corresponding province in cell B2.


"CLR" wrote:

No, I did not say that. I said that Excel cannot by itself determine which
portions of a string are to be considered a city name.

You said, "I cannot extract the City or region names out of
entry text because they are not in a constant position ". That may not
necessarily be true. If there are any consistant conditions surrounding the
city name, such as leading or trailing unique punctuation marks, numbers,
multiple spaces, etc....it may be possible to automatically extract the city
names.

If you can identify some commonality, post a few extreme examples, but do
not attach a file here.


Vaya con Dios,
Chuck, CABGx3



"Roseygains" wrote:

Thanks CLR, unfortanelty I cannot extract the City or region names out of
entry text because they are not in a constant position and also this is a
rather big file which would then just take forever.

So you saying that there is not way I can combine the if, vlookup, match,
search or indext formula.

"CLR" wrote:

A regular VLOOKUP formula will do to locate the city in the list and return
the Province.....that part is easy. First however, you will have to break
out the City name from the text and have it in it's own cell/column. Excel
cannot look at a string of text directly and determine which parts of it
might be considered a city name.

hth
Vaya con Dios,
Chuck, CABGx3



"Roseygains" wrote:

Hi there,
I work in the mobile industry running campaigns and so forth. We received
the sms entry report in a excel spreadsheet.
What I need to do is find the region of entrants. The Entry Data report has
the date, Cell number and text which was entered across columns. The Text
that they have entered will have their City or region in it eg. "Hi I live in
Cape Town"
We'll say that his text is in A2:A100
What I did was, I created a table of Cities of South Africa and in which
province they are in.
We'll say the the City is on Sheet [City] A2:A100 and the Province is on
Sheet [City] B2:B100.
I need to match the name of the city found in the entry text to the name of
the city in the table that I have created and the result should be the
province.
Can this be done ?

city list x text Result
Standerton a 780Ka Bethal Gert Sibande District b
Gert b 194Vg My Name Is Richard Ngema From Standerton a
Kemppark c Peter Mcrae Kemppark 07XXXXXXXX 113Xx c

Given the above, this should work

Sub docities()
For Each c In Range("citiylist")
mr = Range("text").Find(c).Row
Cells(mr, "d") = c.Offset(, 1)
Next c
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Roseygains" wrote in message
...
780Ka Bethal Gert Sibande District
194Vg My Name Is Richard Ngema From Standerton
Peter Mcrae Kemppark 07XXXXXXXX 113Xx

I have the separate spreadsheet listing the name of the cities in cell A2
and the corresponding province in cell B2.


"CLR" wrote:

No, I did not say that. I said that Excel cannot by itself determine
which
portions of a string are to be considered a city name.

You said, "I cannot extract the City or region names out of
entry text because they are not in a constant position ". That may not
necessarily be true. If there are any consistant conditions surrounding
the
city name, such as leading or trailing unique punctuation marks, numbers,
multiple spaces, etc....it may be possible to automatically extract the
city
names.

If you can identify some commonality, post a few extreme examples, but do
not attach a file here.


Vaya con Dios,
Chuck, CABGx3



"Roseygains" wrote:

Thanks CLR, unfortanelty I cannot extract the City or region names out
of
entry text because they are not in a constant position and also this is
a
rather big file which would then just take forever.

So you saying that there is not way I can combine the if, vlookup,
match,
search or indext formula.

"CLR" wrote:

A regular VLOOKUP formula will do to locate the city in the list and
return
the Province.....that part is easy. First however, you will have to
break
out the City name from the text and have it in it's own cell/column.
Excel
cannot look at a string of text directly and determine which parts of
it
might be considered a city name.

hth
Vaya con Dios,
Chuck, CABGx3



"Roseygains" wrote:

Hi there,
I work in the mobile industry running campaigns and so forth. We
received
the sms entry report in a excel spreadsheet.
What I need to do is find the region of entrants. The Entry Data
report has
the date, Cell number and text which was entered across columns.
The Text
that they have entered will have their City or region in it eg. "Hi
I live in
Cape Town"
We'll say that his text is in A2:A100
What I did was, I created a table of Cities of South Africa and in
which
province they are in.
We'll say the the City is on Sheet [City] A2:A100 and the Province
is on
Sheet [City] B2:B100.
I need to match the name of the city found in the entry text to the
name of
the city in the table that I have created and the result should be
the
province.
Can this be done ?

Hmmm........pretty difficult.......normal MID, FIND, etc functions won't work
here.......but one possibility would be a sophisticated macro that would use
the "Contains" selection of the Autofilter, to group rows containing a like
city, by stepping through your list of cities and then filling a helper
column with the desired city names.............unfortunately, it's beyond my
VBA skill level. I suggest if you don't get another good answer here, that
you re-post over in the Microsoft.public.excel.programming newsgroup.

Vaya con Dios,
Chuck, CABGx3



"Roseygains" wrote:

780Ka Bethal Gert Sibande District
194Vg My Name Is Richard Ngema From Standerton
Peter Mcrae Kemppark 07XXXXXXXX 113Xx


"CLR" wrote:

No, I did not say that. I said that Excel cannot by itself determine which
portions of a string are to be considered a city name.

You said, "I cannot extract the City or region names out of
entry text because they are not in a constant position ". That may not
necessarily be true. If there are any consistant conditions surrounding the
city name, such as leading or trailing unique punctuation marks, numbers,
multiple spaces, etc....it may be possible to automatically extract the city
names.

If you can identify some commonality, post a few extreme examples, but do
not attach a file here.


Vaya con Dios,
Chuck, CABGx3



"Roseygains" wrote:

Thanks CLR, unfortanelty I cannot extract the City or region names out of
entry text because they are not in a constant position and also this is a
rather big file which would then just take forever.

So you saying that there is not way I can combine the if, vlookup, match,
search or indext formula.

"CLR" wrote:

A regular VLOOKUP formula will do to locate the city in the list and return
the Province.....that part is easy. First however, you will have to break
out the City name from the text and have it in it's own cell/column. Excel
cannot look at a string of text directly and determine which parts of it
might be considered a city name.

hth
Vaya con Dios,
Chuck, CABGx3



"Roseygains" wrote:

Hi there,
I work in the mobile industry running campaigns and so forth. We received
the sms entry report in a excel spreadsheet.
What I need to do is find the region of entrants. The Entry Data report has
the date, Cell number and text which was entered across columns. The Text
that they have entered will have their City or region in it eg. "Hi I live in
Cape Town"
We'll say that his text is in A2:A100
What I did was, I created a table of Cities of South Africa and in which
province they are in.
We'll say the the City is on Sheet [City] A2:A100 and the Province is on
Sheet [City] B2:B100.
I need to match the name of the city found in the entry text to the name of
the city in the table that I have created and the result should be the
province.
Can this be done ?