We have a list of data that we import into Google Spreadsheets from Wufoo. The data is user generated (user inputs the information into the form). After importing the data into Google Spreadsheets we then have to prepare it so we can import it into our ESP, hence all data has to be consistent. For example, one of the questions we ask is "Where do you go to school." If someone attends The University of San Diego, the answers vary. Some people say USD while others say U of San Diego. When we get something like 1,000 entries, it would be a waste of time to change each field one by one.
Is there a script that I can run that would automatically turn every variation entered into a standard form?
Example- person enters U of San Diego and is automatically turned into USD and so on…
I was thinking something along the lines of: if keyword matches U of San Diego or University of San Diego then convert to USD.
Best Answer
You could just do regexreplace - something along the lines of this ( assuming your data starts just below the header in column B:
You could also place your list of variations anywhere (pretend column A), and just join them with the pipe symbol like this which effectively creates your regex out of a list you can easily keep adding to:
I forgot to mention - because your input is user generated - I suggest making your regex all lowercase, and referencing the data to be switched also in lowercase - thus avoiding any issues with case sensitivity, like this: