Extracting Portion of String

[smmedeiros]

I have an issue simliar to thread767-1008457.

I have this field --- (note single quotes are included in the extract)

Transfer group from 'MGTI GL Data Center Ops' to 'MHRS US Middleware Eng'


I want to be able to extract "from" and "to" information
2 variables - so,
formula1 contains MGTI GL Data Center Ops
formula2 contains MHRS US Middleware Eng

I tried to follow the details from the prior thread and just can't seem to get it right.. getting subscript error messages. Also, I think the quotes around my 2 desired fields are throwing me off.

Can anyone offer the formula solution in order to extract these 2 items.


Much appreciated

 

[kray4660]

Well this could be fun. First off, I would use the instr function to find the word 'from' (example: somevariable := instr({?string}," from ") + 6) ). This should get to the beginning of the first string. Use instr again to find the word 'to'(example: somevariable2 := instr({?string)," to ") + 4) ). This should get you to the end of the first string. Then use the mid function to get the string (example: newstring := mid((?string), somevariable, somevariable2-somevariable+1) ). To get the rest of the string (for formula 2). Use the instr to find the 'to', then use the right function (example: newstring2 := right({string},len({newstring})-somevariable2) ). You may need to add or substract by one (it's been a long time since I have done this). Of course this example expects the variables to be defined properly.

I hope this helps.

 

[lbass]

//{@from}:
stringvar x := {table.field};
extractstring(x,"from '", "' to")

//{@to}:
stringvar x := {table.field};
replace(mid(x,instr(x,"to '")+4),"'","")

-LB

 

[smmedeiros]

Thank you both for your feedback.
LBASS - your solution worked perfectly.


Thanks.

 

[kray4660]

Did not know about extractstring. Will have to look into that one.