Greets, all:

I'm > this < close but just can't seem to nail it.

I'm using a value list that concatenates from another table the values of city, state, and country--that's being used as a key--so it looks like this:

Lewiston|ID|US

Coeur d'Alene|ID|US

Boise|ID|US

As part of another calculation, I want it to extract whatever the two-letter state code is ("ID", in these examples) irrespective of the length of the city name.

I got this far:

Middle ( JOB::Company_City__lxt ; Position ( JOB::Company_City__lxt; "|" ; 1 ; 1 ) ; 3 )

...but I can't figure out a way to dump the pipe ("|") character so I don't end up with "|ID". Also, since the calculation will always result in the format, "|two-letter state|two-letter country", would using the Right text function be more elegant/effective to grab the state code?

Cheers,

Rich

 

Here is one way you could look at it:

GetValue ( Substitute ( YourField ; "|" ; ¶ ) ; 2 )

This "tokenizes" the input and returns the 2nd token - regardless of lengths.

 

6 hours ago, WF7A said:

I can't figure out a way to dump the pipe ("|") character so I don't end up with "|ID".

You need to move the start point 1 place to the right, and extract only 2 characters:

Middle ( YourField ; Position ( YourField ; "|" ; 1 ; 1 ) + 1 ; 2 )

 

6 hours ago, WF7A said:

since the calculation will always result in the format, "|two-letter state|two-letter country", would using the Right text function be more elegant

Maybe. You decide:

Left ( Right ( YourField ; 5 ) ; 2 )

 

---
But of course, if the field is used as a key, you could simply look at the State value in the related record, without having to calculate anything?

 

Wow, all good stuff! Thanks!

...and I'll check out your suggestion about the value in the related record in the morning--unlike you tonight, my brain's dead. : |