Replace function with error coz of the data does not contain

[Jwwwson]

Hi, 

I would like to ask about how should I set up the workflow to replace a string in a column, say from 'TBDstudent1' to 'ABCstudent1' and   'TBDstudent2' to 'ABCstudent2' etc. 

I am currently using the 'Replace' function, however, when a batch of data does not contain a certain string 'TBDstudent1', error occurs. How shall I design the formula or utilise the tool that the replace function only be performed when the column contains such specific target, so that I do not need to modify the workflow each time? 

Thanks!

Cheers,

[grazitti_sapna · Accepted solution]

Hi @Jwwwson , you can use if else condition in the formula tool, as there is just one example for replacing TBD with ABC so it works if you have any other conditions to be specified you can modify the formula and use elseif. Please follow the screenshot for clarity.
Let me know if that helps.

Formula- if Contains([Field1],"TBD") then Replace([Field1], "TBD", "ABC") else [Field1] endif

[ChrisTX]

The REPLACE function itself will not generate an error if the search string is not found.  The error must me somewhere else in your workflow.

Which tool is generating the error?  Can you post a copy of your workflow, including sample input data?

Chris

[Jwwwson]

Thanks @grazitti_sapna ! It works! 

May I ask one more question, does IF function includes two criteria, one is A column(job name) include a specific wordings and the second criteria is column B Does not contain certain wordings, and if the criteria are fulfilled, the wording of another column (Booking Type_) will be changed, otherwise it will remain as original. The formula is shown below, however, the output is always null, would you please kindly advise if anything goes wrong? Thanks!

IF Contains([Job name], "BUSINESS DEVELOPMENT") AND !Contains([Manager], "Peter" OR "Amy") then Replace([Booking Type_], "Non A", "A") else [Booking type] endif

[grazitti_sapna]

Hi @Jwwwson , it is just a minor mistake in the formula you used that is why you are getting null. Yes, you can specify two different conditions in "if" function. Please find the attachment below and let me know if this is what you are looking for.

Formula- IF Contains([Job name], "BUSINESS DEVELOPMENT") AND (!Contains([Manager], "Peter") AND !Contains([Manager], "Amy")) then Replace([Booking Type], "Non A", "A") else [Booking type] endif

Thanks!

[Jwwwson]

Hi @grazitti_sapna, 

Attached please the sample raw data and workflow. Many thanks!

Cheers, 

[grazitti_sapna]

Hi @Jwwwson , I have modified the formula for you and I hope it works as per the requirement. Attaching the screenshot along with the workflow. I hope it helps!

Thanks!

Formula- IF Contains([Job name], "BUSINESS DEVELOPMENT") AND (!Contains([Manager], "Peter") AND !Contains([Manager], "Amy")) then Replace([Booking Type], "Non A", "A") else [Booking type] endif

[ChrisTX]

With your sample workflow, I'm only seeing a warning, not an error.

The warning message is: Warning: Formula (2): Formula: tried to apply string operator to numeric value (Contains) Record #1 in field Booking Type_

This warning message is somewhat misleading.

The problem seems to be with the structure of the second CONTAINS in your IF function.

This syntax is not correct:   !Contains([Manager], "Peter" OR "Amy")

Instead you could use:    [Manager] NOT IN ("Peter", "Amy")

Chris

[Jwwwson]

@grazitti_sapna, Thanks a lot!

[grazitti_sapna]

@Jwwwson welcome.