Extract text from middle of single text string

[Rich S]

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

 

[comment]

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?

 

[Rich S]

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. : |