Extracting a Text Value from a string

[OKCMikeSoll]

Hello,

I have a field that produces a string with a "from" and "to" group imbeded in the text. I would like to have two seperate formulas that extract these group names from the current string. One formula for the "from" group and one for the "to" group. The example below is exactly how it comes out everytime. The only change may be that the group names may have a space in them and some may not. All of the group names are always in Uppercase if that helps.

Current Output Example:
Reassignment from LVL 1 HELPDESK to TELECOM

Expected Output(Formula 1):
LVL 1 HELPDESK

Expected Output(Formula 2):
TELECOM

CR v9.0
Oracle 9i


Thanks in advance
-Mike

 

[vidru]

I can think of a few different ways to do this. This assumes the data is always in this format:
"....[space]from[space]YOUR FROM GROUP[space]to[space]YOUR TO GROUP"

//Formula1
//@FromGroup
Trim(Split(Split(strTest, "from")[2], " to ")[1])

//Formula2
//@ToGroup
Trim(Split(Split(strTest, "from")[2], " to ")[2])

-dave

 

[OKCMikeSoll]

Dave,

Thank you, that worked like a charm. One question though.. If I try to group on one of these formulas I get an error: "A Subscript must be between 1 and the size of the array" and then then it highlights the following section of the formula:

Split({TABLE.DESCRIPTION}, "from")[2]

Any ideas?

Thanks
-Mike

 

[vidru]

Any chance you've got some data that doesn't conform to the original spec? It could also be a mixed case issue (the Split function is case sensitive by default). Try this, and if you still have problems, it likely means there's an unexpected data problem (which can be tested for as well):

//Formula1
//@FromGroup
Trim(Split(Split({TABLE.DESCRIPTION}, "from", -1, 1)[2], " to ")[1])

//Formula2
//@ToGroup
Trim(Split(Split({TABLE.DESCRIPTION}, "from", -1, 1)[2], " to ")[2])

The additional arguments to the Split function will perform a case-insensitive comparison.

-dave