Automate the cleanup of an Excel file, with no macro
Every month the same export arrives with the same defects, and the same ten minutes of manual fixes. Chain the steps here once, watching the result build up: the recipe is saved, and next month it applies in one click, to one file or to every file you drop.
What this tool does
- Eleven steps to combine: clean the export, clean text, convert numbers and dates, restore leading zeros, standardise formats, split or merge columns, remove duplicates, delete or rename columns.
- Each step applies to the result of the previous one; the preview and each step's summary update with every setting.
- The recipe is saved in a small file, to keep, to share with a colleague, to open here again or to drop along with the files.
- Several files dropped: the recipe applies to each, and you get an archive with one file per file and a summary saying which ones to check.
- Columns are named, not numbered: a file whose columns have moved is still processed.
What it does not do: A column renamed in the source file is not guessed: the step looking for it is flagged and the file marked "to check" in the summary. The recipe holds settings only, never data.
No file is uploaded. Turn off your network once this page has loaded: the tool keeps working. How to check it yourself.
Other tools
- Fix a CSV file : Broken accents, everything in one column, lost zeros: a file Excel opens right the first time.
- Remove duplicates : On the columns you choose, optionally ignoring case, spaces and accents.
- Near-duplicates : "Smith Ltd" and "SMITH LTD.": the almost-identical duplicates, reviewed one by one.
- Clean text : Extra spaces, non-breaking spaces, line breaks, invisible characters, case.
- Convert text to numbers : "1,234.56 $", "(45.00)", "12.5-": real numbers that add up.