How to replace certain "words" in a column
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 :)
Solved! Go to Solution.
- Labels:
- Dynamic Processing
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you for your help with the above! :) I was able to figure out the 2nd part! :)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
