I am looking for the formula to find part of the string for example: "Yes" in "No,Yes,N/A,No,Yes,N/A,No" string and if "Yes" exists in the string, then cut out everything else in the same string and live just "Yes" in it
If "No,Yes,N/A,No,Yes,N/A,No" has "Yes" in the string then cut all from "No,Yes,N/A,No,Yes,N/A,No" and live just one "Yes"
if instr({table.string},"Yes") > 0 then "Yes" else ""
You didn't say what you wanted to display if Yes was not present, but you could replace the "" with whatever you want the default to be--the entire field, or "Other", etc.
Tahnks I am getting somewhere with this now...
Now the logic Is this now:
if instr(ITS,"Yes") > 0 then
"Yes"
else if instr(ITS,"No") > 0 then
"No"
else if instr(ITS,"Not Searched Yet") > 0 then
"Not Searched Yet"
However its not doing what i need it to do...Here is the logic I need:
"Yes" is priority choice #1 doesnt matter if there is "No" or "Not Searched Yet" in the string.
Then "No" is priority choice #2 if "Yes" is not there and
"Not Searched Yet" is priority choice #3 if "Yes or "No" is not in the string.
Your formula should work so it might be that your data is in different cases, for example. Also your sample showed "N/A" and no "Not Searched Yet", so is that in the field? Did you want to show "Not Searched Yet" when "N/A" was detected? If so, "N/A" belongs in the instr function. I think you need to put your data in the detail section and observe the variation in it.
You could deal with case by changing the formula to:
if instr(ucase({table.ITS}),"YES") > 0 then
"Yes"
else if instr(ucase({table.ITS}),"NO") > 0 then
"No"
else if instr(ucase({table.ITS}),"NOT SEARCHED YET") > 0 then
"Not Searched Yet"
The problem is that if "Yes" and "No" in the string is not present and all present is "Not Searched Yet" it is always find "No" in the string and the resault displays as "No" and I need in thois case to display "Not Searched Yet" as #3 option
if instr(ITS,"Yes") > 0 then
"Yes"
else if instr(ITS,"No") > 0 then
"No" <--------
else if instr(ITS,"Not Searched Yet") > 0 then
"Not Searched Yet"
I found the solution :
if instr(ITS,"Yes") > 0 then
"Yes"
else if instr(ITS,"No,") > 0 then
"No"
else if instr(ITS,"Not") > 0 then
"Not Searched Yet"
else
""
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.