"City, LongState 12345"...Get each one separately?

[Latbaby]

Howdy FMP Gurus!

This is obviously my first post here and I suspect chuckles will be forthcoming from many of you shortly. I'm hung with what I thought would be an easy calculation.

SCENARIO

I'm trying to get a one line CSZ field into three separate fields. I've read and searched a number of posts here and elsewhere (2 days worth) but have yet to tackle this calc(s).

The text field is currently formatted like this:

"San Mateo, California 01234"

There are no returns, and currently all Zips are five characters long. There is a space between "," and State Name, as well as a space between State Name and Zip. I referenced "San Mateo" because, indeed, some of the city names may have a space as well.

Ideally I'd like a result that is, or is capable of (you guessed it):

City: San Mateo

State: CA

Zip: 01234-1234

I prepped a calc to convert the long state name into a 2-character state name (California to CA), but have yet to use it because I must break the long state name from the text. I have gotten the City name out (Left to ","), and the Zip, being always five characters was no problem (Right 6 and Trim), but the danged long state name is stumping me.

Any suggestions, o gurus?

I thank you for consideration on this matter and hope you make me feel like the simpleton I am.

[LaRetta]

Hi latbaby, welcome to FM Forums!

You can create a calculation to convert it but more simply, you should have a States table which has state long name and abbreviation. Then create a relationship from your main table (the field with the long state) to the States table (the long state) using =.

If you need to get your hands on a states table, there are several if you search the web or I'd be happy to provide one later.

Much would depend upon whether this is a one-time thing or an ongoing process and whether your Users will be updating/changing the information and so forth.

Edited January 14, 2010 by Guest

[David Jondreau]

Middle() and Position() are your friends.

[Lee Smith]

You might find this custom function helpful Link

[Latbaby]

hehe...Middle and Position are drinking all my beer! Here's what I mean:

San Mateo, California 01234 is 27 total characters. I'm having trouble styling the calc to say essentially, TheResult is "27 - (11+6) = 10, oh and by the way, those 10 characters are California!

Will continue to try to make my friends buy their own beer.

Thanks very much for your kind and quick responses.

[David Jondreau]

I've written it out all in one calc, though you may want to split it out for use...

Let([

text = table::field;

leng = Length(text);

comma = Position(text, ",");

Zip = Right(text; 5)

City = Left(text;comma-1);

lengState = leng-comma-5

State = Middle(text; comma+1; lenState);

State = Trim(State);

result = State

];

result

)

I'll take a beer anytime...

Edited January 14, 2010 by Guest

[Latbaby]

Wow Lee...that looks impressive and powerful. Would I build one field calculated to show:

ABC Company(ParagraphSignHere)

1234 Anystreet Dr.(ParagraphSignHere)

San Mateo, California, 012345 ?

Would I create calculation result fields for each "Type" as indicated in: type = "name" ; name ; As well each Developer Parameter name?

[Latbaby]

D J!!! You da man! Gonna bang on it and see where it leads me...probably to the tavern to buy you a big stein of your favorite grog.

Thanks,

Latbaby

[LaRetta]

I thought you wanted to change the long name state to short and that you already had the parsing figured out!

I've attached a file (with a bit different parsing techniques) which addresses the issue if zip is more than 5 and some other stuff. And you will see another table which should hold all the states so it converts properly for you (view the relationship graph and script).

Between all of us, you should have plenty to choose from. :laugh2:

StateAbbr.zip

Edited January 14, 2010 by Guest