Removing N/A from string data

I am cleaning open ended questions and I have some responses where the users input N/A or NA. I have tried the "find and replace" option but am stumped on how to actually cleanse the data to remove responses that have N/A or NA. I choose "Find" but it is automatically filling in selections which are not what I need. Short of removing it from the data before cleaning in Alteryx, the Find/Replace help hasn't helped and I have not really found anything in this group that focuses on that. Any advice on how to clean up N/A from open ended data would be appreciated.

Thanks!

Preparation

Accepted answers

Kanderson

I have adjusted the example to the data you provided. The example is basically the same but I have added a recordID tool as the faculty and term columns were not unique. I have also added a cleanse data tool that handles null values and changes the case of all the text to lower case. As alteryx is case sensitive this will conform the data set for easier matching. The rest is fairly straight forward, simply add strings to the text input tool as needed to replace target strings with blank values.

Not seeing a need for regex here; however, if we need to go that way I think a macro could do the trick.

Adjusted example.yxzp

All comments

Kanderson

A couple of options come to mind. You can use the filter tool to remove records that are equal to or contain the values N/A or NA. If you are looking for more of a find and replace action the formula tool has a function that does this called Replace. You can find it under the string category... It has the following method signature - REPLACE(Str, Target, Replacement). There are likely other options, if you can share an example of how your data is structured that would be helpful.

KAFord

Thanks for your response, I cannot filter those out because I have two columns with open ended responses (these are survey questions that I am matching to the numerical response). For instance, one column has comments that are good but the second column has N/A so filtering would remove the record and I don't want the record removed. On the other hand, I think that Replace and string formula may work. I'll fool around with it and see if that does the trick. I'm a *newbie* to Alteryx so I'm still learning what I can do.

Appreciate the prompt response...will let you know if the filter/replace works. Thanks!

KAFord

I actually had to use Regex and choose Beginning of Line. The only problem is, I need to clean a number of issues like "none at this time" or "nothing to add" so essentially I need to remove verbiage that is written several ways. I guess I have to use Regex multiple times? It would be nice if I could have a bank of strings to remove and do them all at once so if anyone knows how to do that with Regex which does replace the unwanted comments with blanks, that would be wonderful!!!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703