How to text parse "City, State" into City Field & State Field

[JTSmith]

I know it's simple, but text parsing isn't my thing... Any help would be appreciated. I have three fields, one is City State, and then City and State, I just need it to copy the city into the city, and the state into the state.

Thanks!

[Lee Smith]

What does the raw data look like?

[LaRetta]

We need to understand a bit more of the data, JT, to find where to split the two. Is State always two-character? Or is there a comma between them (city and state)?

We need something to identify how to handle the following examples:

St. Louis Missori

Charlotte North Carolina

A computer needs to know where to split it by logic or ... if you happen to have a zip code table handy in another cel (field) of each record? I have one I could scare up if you don't. You can then create a relationship using multiline key to help identify potential two-word city from potential two-word state.

[JTSmith]

City, State

As in: Miami, FL

Always 2 characters

[LaRetta]

City would be:

GetValue ( Trim ( Substitute ( yourTextString ; "," ; ¶ ) ) ; 1 )

State would be same but change the 1 to a 2.

UPDATE: Actually, that was sloppy. Trim it last as:

Trim ( GetValue ( Substitute ( text; "," ; ¶ ) ; 1 ) )

Edited May 23, 2012 by LaRetta

[Lee Smith]

Okay, I['ll be vague too

I could be as simple as

LeftWords (yourfield ; 1 ) for City RigntWords(yourFieid; 1) for State.

However these will break depending city being one word and state being the last word to the right.

[LaRetta]

LOL, JT, you changed the specs. Luckily it should still work if there is always a comma between city and state.

But you can also get city then by: LeftWords ( yourTextString; WordCount ( yourTextString) - 1 ) ... the trim unnecessary