Clean up misspellings in text field (address)

[generalledger]

I am trying to clean up misspellings in 65,000 street addresses. I thought if I deleted all the good words, only the bad words would remain. Close and Load to a Table in Excel. Then I can address the misspellings.

In Power Query, I duplicated the Address column (Address-Copy). Then I added many Replace Values steps to pull out acceptable words (Avenue, Street, Lane, First, Second, Third, Oak, Spruce, Pine, etc.) and replace them with nothing. Theoretically, any remaining words are misspelled. The challenge is there are about 100 acceptable words, which requires 100 Replace Value steps. The size of the query is out of hand and slow.

Can you suggest a better approach?

Is there a way to create a list of acceptable words in Excel and feed that list to Power Query so all of the words in the list are replaced by nothing, leaving behind only "invalid" words?

Thanks to everyone for their support.

 

[Logit]

.
Here is a formula example using SUBSTITUTE.

https://www.howtoexcel.org/vba/how-to-find-and-remove-multiple-text-strings-within-a-text-string/



And here is a VBA method :

https://superuser.com/questions/937360/how-do-i-delete-specific-text-in-multiple-cells-at-once

 

[horseyride]

See download file @ http://www.thebiccountant.com/2016/05/22/multiple-replacements-in-power-bi-and-power-query/