2

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.

3

You could just do regexreplace - something along the lines of this ( assuming your data starts just below the header in column B:

=ARRAYFORMULA(REGEXREPLACE(B2:B,"U of San Diego|University of San Diego","USD"))

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:

=ARRAYFORMULA(REGEXREPLACE(B2:B,JOIN("|",A2:A),"USD"))

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:

=ARRAYFORMULA(REGEXREPLACE(lower(B2:B),JOIN("|",indirect("A2:A"&counta(A:A))),"USD"))
  • Awesome! Follow up question... Since I'm dealing with multiple schools, how can I make it so that each school is turned into its respective variation... Example University of San Diego --> USD and University of Phoenix --> UoPx... Hope that makes sense... – Edgar Jan 21 '16 at 17:40
  • im all ears.... – Aurielle Perlmann Jan 21 '16 at 17:41
  • yeah one sec let me modify, brb - btw are we talking like tens of schools - or can you count the variations on one hand? Also - maybe a drop down menu option on wufoo might help for the school situation? – Aurielle Perlmann Jan 21 '16 at 17:58
  • You can keep wrapping the regexreplaces around your formulas like this: =ARRAYFORMULA(REGEXREPLACE(REGEXREPLACE(A1:A,"U of San Diego|University of San Diego","USD"),"university of phoenix","UoPx")) – Aurielle Perlmann Jan 21 '16 at 18:05
  • Awesome... Getting close... The formula above works but when I add the JOIN part to it, I get this error "Error Text result of JOIN is longer than the limit of 50000 characters."... Any idea why? Thanks again for the help! – Edgar Jan 21 '16 at 19:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.