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!

What does the raw data look like?

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.

City, State

As in: Miami, FL

Always 2 characters

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, 201213 yr by LaRetta

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.

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