identify city, state zip
 

I'd like to identify whether a cell contains a city state & zip. This could
be "New York, NY 10005-1234" or "Boston, MA 12345". I've been thinking
maybe I could key in on the ", AA #####" sequence to identify it, using
strMyString Like ", AA #####". Is this the best way? If so, is "A" a
wildcard for characters like "#" is a wildcard for numbers? Also, how do I
make the Like command operate on only these 9 characters within the string?
Thanks. You gurus are geniuses.

Dave

identify city, state zip
 

Hi Dave,

I'd like to identify whether a cell contains a city state & zip.

"Boston, MA 12345"
"New York, NY 10005-1234"

No way to check this by an algorithm.
Let me give You a short introduction into linguistics.
You'll need a list of all states, the so called lexicon,
"the full set of irregularities".

As "XX" is no state, I think, but consists of 2 letters.

Then you have to check whether characters 2 and 3 after
the comma combined are in this list.

Then it seems, that a zip code should only consist
of one or more of these: "0123..." plus an optional minus (-)
followed by one or more of "0123..."

For that you can set up a rule, the grammar.
There has to be one or more digits,
then a minus (-) may follow, wich would require again
one ore more digits.

Can all be done.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

identify city, state zip
 

Dave,
This ought to get you fairly close, but I don't control
what goes in the cells. For example "Cheese, Be 00000 Whiz"
would show as True.
Something written using Regular Expressions might be able
to do the job 100% of the time, but I am the wrong guy
to generate that code.

Regards,
Jim Cone
San Francisco, USA
'------------------------
Sub TestLike()
Dim blnAnswer As Boolean
Dim strMyString As String
strMyString = "*, [a-z][a-z] #####*"
blnAnswer = (Range("B6").Value Like strMyString)
MsgBox blnAnswer
End Sub
'-----------------------



"Dave B"
wrote in message

I'd like to identify whether a cell contains a city state & zip. This could
be "New York, NY 10005-1234" or "Boston, MA 12345". I've been thinking
maybe I could key in on the ", AA #####" sequence to identify it, using
strMyString Like ", AA #####". Is this the best way? If so, is "A" a
wildcard for characters like "#" is a wildcard for numbers? Also, how do I
make the Like command operate on only these 9 characters within the string?
Thanks. You gurus are geniuses.
Dave