No. You would have to modify the formula starting and ending characters for the characters that frames the text you are looking to extract from the string.
I believe if I could narrow the field selection down to those records that have a number from 1 to 9 following the X I could create some consistency in the field? The above formula was the only thing I could come up with, but I am stll getting an error starting with Mid.
May I suggest an alternate formula which splits the string into pieces based on the existence of '/' and then evaluates each piece to see if 'x' exists and if the next character is numeric then display the text next to 'x' else blank.
For example:
// X is the string field
stringvar array a := split(x,'/');
stringvar z := '';
numbervar i;
for i := 1 to count(split(x,'/')) do
(
if instr(a<i>,'x') > 0 and a<i>[instr(a<i>,'x')+1] in '0' to '9' then
(
z := z & a<i>[instr(a<i>,'x')+1 to length(a<i>)];
exit for;
));
z;
If the data string happens to be a2/zpwv/q5/r8/x123x5 then it will return 123x5. Also, once it finds a matching condition it will quit and will not process the string further, if you want it to continue processing, just comment out the EXIT FOR statement.
For example, if the string is x2/a2/zpwv/q5/r8/x123x5 the result will be 2.
Just copy the following as is into a formula and run it. I do not get any errors when I run my original formula. The error comes into play when you substitute X with {@Des-Data Extraction} in some places only.
evaluateafter({@Des-Data Extraction});
stringvar x := {@Des-Data Extraction};
stringvar array a := split(x,'/');
stringvar z := '';
numbervar i;
for i := 1 to count(split(x,'/')) do
(
if instr(a<i>,'x') > 0 and a<i>[instr(a<i>,'x')+1] in '0' to '9' then
(
z := z & a<i>[instr(a<i>,'x')+1 to length(a<i>)];
exit for;
));
z;
This formula will evaluate all splits and return only the one with the number next to it (if any).
If you have copied the formula exactly as is and not made any changes, then I am not sure why it is giving errors, unless CR 10.5 cannot handle array manipulation. I ran the formula in CR 2008, CR XI R1 and CR XI R2 and it works just fine without any errors. I have no way of testing it in CR 10.5.
Can anyone else confirm the limitation of CR 10.5?
If that is the case, then we are back to square one. Let me see if I can re-work the original formula, unless someone beats me to it
I have a working formula that pulls the number to the right of the x already. The problem is users are enter comments before the x such as words like fixed.
fixed sc x3/zsb/q1/u5
All I really need is something that will look for the first instance of an X with a Number after it and then return everything to the right of it. I just want to eliminate anything before that X3.
OK here is a modified formula without any arrays. It has limitations in that it will process up to two 'x's being present in the string. For example: fixed sc x1113/zsb/q1/u5 will work fine or one without any X but if the string has 3 Xs then it wont work unless the ELSE part is modified.
stringvar s := {@Des-Data Extraction};
if instr(s,'x') > 0 and mid(s,instr(s,'x')+1,1) in '0' to '9' then
Mid (s, Instr(s,'x')+1,(InStr(s,'/') - InStr(s,'x') -1))
else
(
s := replace(s,'x','.',1,1);
if instr(s,'x') > 0 and mid(s,instr(s,'x')+1,1) in '0' to '9' then
Mid (s, Instr(s,'x')+1,(InStr(s,'/') - InStr(s,'x') -1));
);
stringvar s := {@Des-Data Extraction};
//
if instr(s,'x') > 0 and mid(s,instr(s,'x')+1,1) in '0' to '9'
and instr(mid(s,instr(s,'x')+1,length(s)),'/') <= 0 then
Mid (s, Instr(s,'x')+1,length(s))
else
if instr(s,'x') > 0 and mid(s,instr(s,'x')+1,1) in '0' to '9' then
Mid (s, Instr(s,'x')+1,(InStr(mid(s,instr(s,'x')+1,length(s)),'/')-1))
else
(
s := replace(s,'x','.',1,1);
if instr(s,'x') > 0 and mid(s,instr(s,'x')+1,1) in '0' to '9' then
Mid (s, Instr(s,'x')+1,(InStr(mid(s,instr(s,'x')+1,length(s)),'/')-1))
);
PS: If the formula fails, please provide the field value (displayed on the left hand side in the formula workshop) so that it is easier to debug rather than having to "figure out" what might have caused the problem.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.