I'm working on a Trifacta recipe where I need to replace some cell values based on conditional text strings within. I banged my head against the Replace and Pattern functions but I'm still not getting what I need.
If a Cell in a column contains "ABC123" I want to replace the entire cell text with "XYZ"
ie
Current NEW
"ABC123 HOME" "XYZ" [full cell value is replaced based on containing the pattern "ABC123"]
"ABC123 WORK" "XYZ" [full cell value is replaced based on containing the pattern "ABC123"]
"ABC123" "XYZ" [full cell value is replaced based on containing the pattern "ABC123"]
"HOME" "HOME" [does not contain defined pattern, remains unchanged]
So far my attempts have only replaced the defined pattern, but not the rest of cell contents, resulting in "XYZ HOME" "XYZ WORK", etc
I know i can set up individual find/replace rules for the full cell values, but I'd prefer to do this based on the pattern to futureproof it somewhat.
Hoping someone can point me in the right direction on this. Thank you!!
