yogi_Correct Entity Names in Columns Of A Table Based On Entries In A Correction Template

                                          Google Spreadsheet   Post  #1027

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 11, 2013
user jake de snake said:(http://productforums.google.com/forum/?zx=4wq9qb6tdj8l#!category-topic/docs/spreadsheets/MBotFw-IOeo)
how can i keep a defined list of known terms that should be automatically corrected, in a dynamically imported list?
Oh hai

First time writer here, and on groups in general i think! I'm guessing i'll be returning with lots of questions.

Anyhow. Here is a spreadsheet, where i'm importing a country list. This one comes from Wikipedia, and in a practical case this could be a much longer list.

https://docs.google.com/spreadsheet/ccc?key=0AjUYAokAtK73dFdtME41UncxcGVNVkdxY3ByeHdBMXc&usp=sharing

Now, lets say for the sake of this example only, that "Mozambique" would be an old or incorrect name and the new name is "Mocambique". Same for "Macau", which we want to call "Macao".

These two terms i know about, by heart, and have therefore entered them to a manual list on the sheet "template". My goal now would be to have the list by applying the terms in the template sheet, a search+replace option.

How would this be done -- i suppose i need to write a function? I have yet to write any (script) functions entirely on my own, but maybe this would be a good primer?

The reason for this question is almost like the example: typically i sometimes want to import long country lists, and i need those nations to have their most official names, so they can be mapped to other tings later.

Also, i've prepared a "correction" sheet here, where the output is supposed to appear. But could things like this be applied directly to the import sheet, instead?

Regards

Jake

-----------------------------------------------------------------------------------------------------------------

Although a script based solution would work better in this case, I have provided a formula based solution in the following