Last week, our community delved into a variety of pressing issues faced in the data entry world. Members extensively discussed the limitations of double-entry systems and the headaches caused by inconsistent date formats during data imports. There was also lively conversation around unexpected glitches in Excel, such as incorrect country names, and the subtle yet frustrating problem of trailing spaces. Additionally, practical solutions like AutoHotkey snippets and the efficiency of OCR for processing receipts were explored.
This Week’s Hot Topics
When double-entry stops being enough
The community is questioning the reliability of double-entry systems when errors persist. It’s a crucial topic for those concerned about data accuracy. Read more here
Consistent date formats on import
Inconsistencies in date formats can lead to major data issues. This thread offers practical tips on how to maintain uniformity. Read more here
Excel invented 38 new countries for me
A humorous yet serious discussion on how Excel’s quirks can lead to unexpected errors, like creating non-existent countries. Read more here
Defeated by a trailing space
Something as simple as a trailing space can wreak havoc on data accuracy. Discover how to spot and fix these sneaky issues. Read more here
AutoHotkey snippets that shave minutes
Learn about time-saving AutoHotkey scripts shared by members that can streamline repetitive tasks. Read more here
Is OCR actually faster for receipts
Debate continues on whether OCR technology truly speeds up receipt processing compared to manual entry. Read more here
That’s it for this week’s digest. Feel free to join the discussions or share your own experiences. Have a productive week ahead!
incorrect country names Yep, I’ve had Excel quietly switch “Turkey” to “Türkiye” in a US file — ugh. I lock entry to ISO-3166 alpha-2 codes with Data Validation and use a lookup for the display name; in Power Query I also set the import locale so dates don’t flip on load. Slight trade-off in setup time, but it’s made our weekly imports boring in the best way.
Yeah, this is rough. The only way I’ve kept my sanity is to make one small change per cycle and ignore the rest until there’s real guidance.
On the import side, I’ve cut down dd/mm vs mm/dd chaos by using Power Query’s ‘Change Type with Locale’ and explicitly setting the date column to the source locale (e.g., en-GB for UK files), which holds even if the workbook’s regional settings change.
Quick tip that saved me last week: I keep a tiny alias table (Türkiye→Turkey, Côte d’Ivoire→Ivory Coast, Eswatini→Swaziland) and run XLOOKUP post‑import to normalize to ISO‑3166‑1 names: https://www.iso.org/obp/ui/#search/code/. Caveat: if someone used Excel’s Geography data type, it can still flip to local names based on Office language — so I set the entry column to Text upfront to stop the auto-change ().
Had Excel silently flip a country name when Text/CSV import auto-detected types; setting the dialog to “Do not detect data types” and landing the file on a hidden Staging sheet as plain text stopped it. Small caveat: dates arrive as text too, so I convert only the date columns afterward and leave country strings untouched (@Nora, ref: Import or export text (.txt or .csv) files - Microsoft Support). Much saner than the double-entry workaround folks were debating last week.
We wired Data Validation on the Country column to a hidden RefCountries list we refresh weekly from https://download.geonames.org/export/dump/countryInfo.txt, so if Excel “helps” and renames something it’s flagged immediately; @jgraham83 small caveat — tick Ignore blank so partial uploads don’t nag.
Stopped fighting CSV import and push countries through Power Query: strip diacritics (Text.Normalize to FormD, then remove marks) and do a fuzzy merge to an ISO-3166 lookup so ‘Cote d Ivoire’ maps cleanly. Set the similarity high and eyeball ‘Congo’ and ‘Guinea’ hits to avoid bad matches. +1 to @jgraham83’s controlled list; PQ just gets me there faster on messy files.
Ran into Excel rewriting country names when it silently turned the column into the Geography data type — . In File > Options > Data, I unchecked “Automatically convert to Geography” and set the Country column to Text before paste, and the names stopped morphing. If you do need Geography, flip it back on but only on a separate review sheet so your raw import stays clean. @macaw6189 might like that trick since it avoids fights with auto-detection.