Jump to content

Extract text from middle of single text string


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

Recommended Posts

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

 

Link to comment
Share on other sites

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?

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 1636 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.