Replace a value with formula

Question

Hi,
I am looking for a formula which replace the content of a cell by a fixed value which will always be the same. I think this kind of formula does exists but I don't remermber which one it is.

Here is an example of what I want to do:
Where column name = "CDC": If(CDC="888888", 'replace with the value "XXXXXX', CDC")

Does anyone know how I can do this?

Thank you in advance,

Noémie
#CostingStandard(CT-Foundation)

Accepted Answer

Use the Substitute function.
https://community.ibm.com/community/user/viewdocument/substitute-function?CommunityKey=7a610da7-1c62-4ccf-81e0-01925811038c&tab=librarydocuments
It will replace the string of text you specify with the string of text you want.

=Substitute(CDC,"888888","XXXXXX") where CDC is the column that contains the text to test, "888888" is the text you're searching for, and "XXXXXX" is the text you want to use as replacement text.

Rather than using an IF statement, if you can consistently predict that every single time 888888 appears it will need to be replaced with XXXXXXX, then you won't need an IF statement. You can just use =Substitute(CDC,"888888","XXXXXX"). The formula will only substitute 888888 when it occurs and will leave all other text as is.

For tracing and troubleshooting I would recommend using a new column named something like CDC Replacement and use the Substitute formula with the new column. That way your original data will remain intact if you ever need to trace things back through your data transformation.

Let me know if this helps.

#CostingStandard(CT-Foundation)

IBM Apptio