This is my first posting on FMForums so I don't know what to expect...

My problem is that I have data (being a city name and a county name) in one field that is seperated by a comma. I want to seperate the two from each other and get them in seperate fields. I immediately tried to use first word and last word functions but realized it didn't work--of course the cities are sometimes one or two words as well as the counties. Is there a function that I can use that will recognize the comma in the middle and pull out data respectively? Help!!

Edited September 28, 200520 yr by Guest

You could use a Position function to determine where the comma lies and use the comma as a delimiter, but it is probably easier to substitute a carriage return for the comma and calculate using the Right and LeftValues functions.

City:

Substitute( LeftValues( Substitute( yourField; ", "; ¶ ); 1 ); ¶; "" )

County:

Substitute( RightValues( Substitute( yourField; ", "; ¶ ); 1 ); ¶; "" )

If there is no space after the comma in your field, remove the space following it in the calculations.

I am very impressed!!! I tried it and it worked. Thanks a million

My first experience with FM Forums turned out to be great...