Parsing text from the right

[Rich]

I always get hung up trying to create a working calculation when parse text from the right side of the field. (To my credit at least I'm consistent.)

I have a text field that concatenates values from three different fields into one, e.g., Lewiston|ID|US, where left of the first pipe is the city, the two letters between the pipes is the state, and the last to characters are the country. State and country are always two characters long whereas city can be any length.

Extracting the country's text is easy: Right ( textfield ) ; 2 )

...it's parsing the city and state text where I'm flummoxed. I'm pretty sure the Length and Position commands come into play but I just can't nail the syntax correctly. Any help would be appreciated. Thanks!

Edited August 23, 2018 by Rich
Grammar

[Lee Smith]

How about posting an accurate representation of the text. Including, spaces, special characters, etc.

A copy of the calculation you tried would also help.

[Rich]

Well, for example: to grab the second group of characters this calculation works...

Left ( Right ( textfield ; 5 ) ; 2 )

...but it looks goofy--I'm sure there's a more elegant way of coding that. 

[Lee Smith]

2 hours ago, Lee Smith said:

posting an accurate representation of the text.

This is the key for seeing what the calculation should be

Left and Right can break.

[Rich]

I guess I don't follow: the "accurate representation of the text" would be these examples:

Lewiston|ID|US

Clarkston|WA|US

Victoria|BC|CA

..etc.

 

I think I cracked the city part of it, though; it only took four hours of tinkering with the calculation:

Left ( textfield ; Position ( textfield ; "|" ; 1 ; 1 ) - 1 )

[Lee Smith]

For what I mean, see this thread https://fmforums.com/topic/102645-help-parse-text-calculation/?tab=comments#comment-466805

Are the Pipes always there?

 

 

[doughemi]

Let(
list = substitute(yourField; "|";¶);
$city = getValue(list; 1)
)

Then, $state = getValue(list; 2) and $country=getValue(list; 3)

Or

Let(
[firstpipe=Position(yourField; "|"; 1; 1);
secpipe= Position(yourField; "|"; 1; 2);
$city = Left(yourField; firstpipe-1);
$state= Middle(yourField; firstpipe+1; 2);
$country =Right(yourField; Length(yourField)-secpipe)
];
...
)

 

Edited August 23, 2018 by doughemi

[Lee Smith]

Hi Rich, 

The reason I was digging for information, as there are usually more than one way to reach the end goal.

And by seeing the RAW TEXT we can see the best way to your solution. As you see from my previous posts, there can be unexpected results.

• doiughemi provided the way I would do it.

However, here is what I think you were trying to accomplish.

LeftWords ( textField ; 1 ) //Lewiston

MiddleWords ( textField ; 2 ; 1 )  //ID

RightWords ( textField ; 1 )  //US

HTH

Lee

[Rich]

10 hours ago, Lee Smith said:

Are the Pipes always there?

 

Yes, they're always there.

Thank you both VERY much for your insight and help--I really appreciate it!

[Rich]

Just a follow-up:

Things went well until I entered the city, Coeur d'Alene--the apostrophe changed the number of "words" so for now I'm using...

Left ( textfield ; Position ( textfield ; "|" ; 1 ; 1 ) - 1 )

...to grab the city and...

Let ( list = Substitute ( textfield ;  "|" ; ¶ ) ; GetValue ( list ; 2 ) )

...to grab the state.

[Lee Smith]

Hi Rich,

Parsing isn't something where, one-size-fits-all. You do have to verify that the Calculation works on your text as you envision, by proofing it like you did. The pipe characters are rare in Raw text, and usually used when concatenated a field. Hence the reason for my questions. The reason is, the RAW Text sometimes provides better keys for this use.

Let ( [
	~f = YourField ;
	~l = Substitute ( ~f ; "|" ; ¶ ) 

];
	GetValue ( ~l ; 1)
)

 

[Rich]

Thanks, Lee--I like the simplicity of your calc.

As an aside, I have to keep in mind to keep my FileMaker Pro coding as simple as possible since if I'm run over by a truck, our school will need to find another FileMaker Pro person to take my place ...who may not have power-user or developer-type skills.

Edited August 24, 2018 by Rich

[Lee Smith]

Hi Rich,

I modified my text above to make it sound better.

Your welcome for the Let Calculation.