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 ?