Separating data in a field? 1

[csmajor1]

Currently, I have a record field that contains both a city and a state. I am trying to grab the data that is currently in the one record and have a individual city record and then an individual state record. The problem is:
there are city's that contain more than one word (ex. New York NY) There is also no comma's. I do not know how to grab the cities complete name with out a comma present and I do not know how to read the string until it reaches the state name without reading the string until it reaches a comma. Do you have any suggestions or helpful hint? Thanks for your time and consideration.

 

[Turkbear]

Hi,
Unless there is some way ( in the data field) to determine when the State part begins, there will be no way to do this..
Are all State parts 2 characters at the end of the field?
If so then the Right() function can get it...








To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[csmajor1]

Thanks for responding. The state is only 2 characters from the right here is what mine looks like and I am still getting errors:

=If IsNull(<Addr Line 3>) Then <Addr Line 2> Else Right(<Addr Line 3> ,2)

Both Addr Line 3 and Addr Line 2 contain the same thing. (ex. city state)

Also, how about the city....How do I go about grabbing these characters. I am getting a syntax error with this statement below:

=If IsNull(<Addr Line 3>) Then <Addr Line 2> Else Left( <Addr Line 3> , Length(<Addr Line 3>))-2

Can you help?

 

[kskid]

Try this

//@state
if not isnull({address.line3}) then
  right({address.line3},2)

//@city
if not isnull({address.line3}) then
  trim(left({address.line3},length({address.line3)-2))

 

[csmajor1]

Thanks so much kskid. I finally figured it out and your example was very helpful. Keep up the good work. Happy Memorial Day!!!!