How to normalize inconsistent spelling in Excel file with +1000 rows?

[temeraire007]

Hi folks,
I have an Excel file on which I want to run some analysis (sum if, count if, etc.). However the problem is my data set is inconsistent with abbreviations, spelling mistakes, etc. So for example (Germany, Deutschland, GER, germany, gerany,...)
How can I align and harmonize this?
Thanks

 

[felixstraube]

could you share you file or a sample (XL2BB or upload file to cloud storage and post link) of it and explain what result you want in each case?

 

[temeraire007]

I have trouble uploading for some reason but here an example:

Value	Targeted outcome
Germany	Germany
germany	Germany
GER	Germany
DE	Germany
gerany	Germany

 

[felixstraube]

You could try a lookup table with all the different values and the corresponding country
Like so:
Where D1:E5 is your lookup table which you will need to complete

Libro1
	A	B	C	D	E
1	Country	Correct country Lookup solution		Input	Output
2	Germany	Germany		Germany	Germany
3	germany	Germany		GER	Germany
4	GER	Germany		DE	Germany
5	DE	Germany		gerany	Germany
6	gerany	Germany
Hoja1

Cell Formulas
Range	Formula
B2:B6	B2	=XLOOKUP(A2,Tabla1[Input],Tabla1[Output])

 

[preparationhh]

There is also a couple of tools you can use. They are not perfect, but for me for large files like +5K data entries saved some time.
DataNormalizer (Online solution, so no need to download anything, got quiet good results quickly)
OpenRefine (will have to download the tool but its really powerful to automate)
TableAnalyzer (tool from Microsoft but I think only works for MicrosoftAccess)