OpenRefine (https://openrefine.org/) is your friend. Bit of a learning curve, and still takes a lot of manual effort, but it reduces it to the essential amount needed. If you have more than a thousand records or so, time learning it pays off. It's FOSS and based on an earlier Google project (I hear they have some experience with data). There are probably commercial apps/services in the same genre, but OpenRefine did what I needed on a couple data-cleaning projects. Much better than throwing a grep party, or $DEITY forbid, trying to clean dirty data with a spreadsheet.