We need an Excel Macro that performs three functions for us, which are: 1) change numbers to the number 1 in specified columns, 2) change certain phrases to 0 or * and 3) Change % to straight numbers and eliminate the percent phrase before numbers and other similar alterations described below.
1) Specify Columns and Change all numbers that are not "1" to 1. For example, specify columns c4-c17 and in each column change all numbers to the number 1.
2) For the entire spreadsheet except for open ended questions, change phrases in phrase set 1 to * and change phrases in phrase set 2 to 0.
Phrase set 1: Unknown, don't know, NA, n/a,Not Available, N/A, unsure, not sure, do not know, I don't know, I don't have that info, No info, I don't have that information (Change to the symbol *)
Phrase set two: None, zero, nothing (change to the number 0)
Should also include a utility to add phrases to the lists
3) For the entire spreadsheet with certain columns specified as exceptions Change % to Straight numbers so that 10% is simply 10
And also change 10 percent to simply 10
And 10 percent to simply 10