Jump to content

Parsing text from the right


This topic is 2043 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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 by Rich
Grammar
Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 )

Link to comment
Share on other sites

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 by doughemi
  • Like 1
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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)
)

 

Link to comment
Share on other sites

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 by Rich
Link to comment
Share on other sites

This topic is 2043 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.