CODEWITHSUNDEEP
databasegoogle-sheetsfind-replace
Claire Menegus
Claire Menegus

Reputation: 21

Bulk Find and Replace from a Master List in Google Sheets

I have a spreadsheet in Google Sheets. We currently pulled it from Ooyala to run QA. We are trying to eliminate manual/human QA, and properly format many different values. An example of a row is

Header Row
Hello
hello
Yes
yes
Bye
bye

Is there a way to write a script that targets a column, for example Header Row, and then finds and replaces an entire master list of values? For example, somehow loading in a master doc that replaces all instances of hello with Hello and yes with Yes in one full swoop? Would also be willing to try R or some other platform.

Upvotes: 2

Views: 2030

Answers (2)

Aurielle Perlmann
Aurielle Perlmann

Reputation: 5529

You can just combine the PROPER function along with arrayformula and a condition to check for text:

=ARRAYFORMULA(IF(ISTEXT(A:A),PROPER(A:A),))

enter image description here

or if you want the final list of values to be unique you can also do:

=ARRAYFORMULA(UNIQUE(IF(ISTEXT(A:A),PROPER(A:A),)))

enter image description here

Upvotes: 0

Robin Gertenbach
Robin Gertenbach

Reputation: 10816

If the example column is column A you can put in a free column:

=ArrayFormula(IF(LEN(A2:A), UPPER(LEFT(A2:A,1)) & RIGHT(A2:A, LEN(A2:A) - 1), ""))

And then copy paste-values the resulting column into column A.

Upvotes: 2

Related Questions