How to quickly replace ‘misspelled’ words in Excel

Q. I receive a weekly sales report from our European affiliate that has between 1,260 and 2,100 city names spelled using a native spelling that does not match what we use here in our offices (42 city names “misspelled” 30 to 50 times each). To correct these spelling issues so the city names can be used to merge their data with our data, I perform 42 separate search-and-replace operations each week, which takes a lot of time. I’m working to have the spelling problems corrected at the affiliate offices in Europe, but in the meantime, is there an easier way to correct them than using Word’s Search and Replace tool?

A. If this problem is a simple matter of different spellings, then instead of performing 42 individual search-and-replace operations, you can press the F7 key to spellcheck your entire worksheet or workbook. In this case, if you use the Change button (as it sounds like you do), this spellcheck operation requires you to correct each spelling error one at a time — up to 2,100 times. Fortunately, there is a better way to use the spellcheck tool. Instead of pressing the Change button for each misspelled word in the document, you can correct all of your misspelled cities as larger blocks of corrections (one block of corrections for each city name) by rapidly clicking the Change All button 42 times, as pictured below. This approach results in a single spellcheck operation for the entire document, which is much faster than performing 42 individual search-and-replace operations.

If your Excel’s spellcheck dictionary does not contain correct spellings for all cities involved, you can add them to your dictionary as follows. From Excel’s File tab, select Options, Proofing, Custom Dictionaries, and then select the default dictionary (as pictured below), click the Edit Word List button, and add the correct city spellings to your dictionary.

However, if the problem arises because the European affiliate uses common native names for cities, such as Firenze instead of Florence (Italy), Sevilla instead of Seville (Spain), or Wien instead of Vienna (Austria), then spellcheck won’t do the trick. In this case, you might consider creating a macro to replace these city names as follows: From Excel’s View tab, select Macros, Record Macro, and in the resulting dialog box enter a name in the Macro name box such as replace_city_names, and click OK, as shown below.

Next perform the 42 search-and-replace operations as you have been doing, and when you have completed those operations, from Excel’s View tab, select Macros, Stop Recording. In the future, you can run this macro to have Excel replace all 42 city names for you in one simple step. To run this macro, from Excel’s View tab, select Macros, View Macros, select the replace_city_names macro, and press the Run button. These macro instructions work the same in Microsoft Excel and Word.

About the author

J. Carlton Collins, CPA, (carlton@asaresearch.com) is a technology consultant, a conference presenter, and a JofA contributing editor.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.