How to replace certain "words" in a column

[abae]

Hello,

I am trying to figure out if there is a way (or formula) that will replace the below in a specific column:

Eg. If a word is "ABCDE". then replace with "12345", if "CCCC" then replace with "9999", if "HHHH" then replace with "8888", and can set other values/words to blank.

Basically, there are various data in a specific column, but all I care is the above 3 letters "ABCDE", "12345", "CCCC" to have them replaced to above, and blank out the rest.

In addition, is there a way to also add up all the "CCCC" and "9999" and "8888" together once the above have been replaced? Like there will be multiple CCCC, and 9999, and 8888 that I want to add them (so 3 different totals, not all together).

Thank you very much :)

[PanPP · Accepted solution]

Hi @abae 

1) You can leverage the Find Replace tool - https://help.alteryx.com/20223/designer/find-replace-tool

- If you right-click the tools at the top of Designer and click open example, each tool has an example you can view for reference.

In the Find Replace tool, you can replace certain portions of a value in a column. You can use a text input tool to create a key for the values you would want to replace.

2) You can also use an expression in the formula tool - Replace(String, Target, Replacement)

3) You can leverage a summarize tool to add up all the values based on a group by value. 

- Can you elaborate on what you mean by 3 different totals? 

If you provide some sample data with what the output would look like, I can provide a sample WF.

[Felipe_Ribeir0 · Accepted solution]

Hi @abae 

The replace part can be done using this formula:

Switch([Field1],Null(), "ABCDE","12345",
			"CCCC","9999",
			"HHHH","8888")

The second part about adding the values, i didnt get it. If you could provide a sample input/output, it would help a lot.

[abae]

Thank you for your help with the above! :) I was able to figure out the 2nd part! :)

[rupeshkotecha]

You could also use the RegEd Tool or a IF THEN statement.