Removing N/A from string data

[KAFord]

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!

[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!!!

[Kanderson]

If I understand what you are doing, the attached workflow should provide a very basic example. Essentially, we have a text input tool mocking up some survey responses, and another with the key words you want to find and remove. Notice the second column (the replace word) is empty, but you can update that if you like. First we pivot the responses so that all the answers are in a single column. Now we can use a single find and replace tool before pivoting the data back to its original format. 

Hope this helps.

[KAFord]

I am attaching the Excel file that I exported the open-ended comments. The first section is the original data and the second section was after I used the Regex with the following:

Parsing OE2 with the following:

^None At This Time.

Output Method: Replace

Notice in the example that by using this option I was able to replace the "None At This Time with blank, which is what I want.

I would also like to remove N/A from the comments but I would hope that I do not have to drop multiple Regex onto my canvas...it would be nice to have multiple expressions in the one Regex parse so I run it one time and it creates blanks for the verbiage that is not needed.

I'm hoping that makes sense. I'll try your way as well, I will fool around using the flow you sent.

Thank you again for your help and assistance!

[Kanderson · Accepted solution]

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. 

[KAFord]

I agree that I don't need Regex and that your way will definitely work. I have unique IDs in my full data set, I just removed those to send the example. I played around with it last night and still struggled a little..I wasn't getting the option to choose the ReplaceWith like the example you shared but that could be due to the fact that I didn't have a unique ID.

I can't tell you how much I appreciate your help since Alteryx is new to me and I haven't done any real training yet. Since one of the key reasons I purchased this product is to help with all the surveys we use, I jumped right in.

Thank you so very much for your time and guidance!

--Kim