Notepad++ Tricks for Cleaning Data Before Excel

ERP and bank exports often arrive as messy text. Notepad++ (free) fixes them in seconds.

• Column mode: hold Alt and drag to select a vertical block — delete a column of junk or type the same text on 500 lines at once.
• Remove blank lines: Edit → Line Operations → Remove Empty Lines.
• Remove duplicates: Edit → Line Operations → Remove Duplicate Lines.
• Sort lines: Edit → Line Operations → Sort Lines Lexicographically.
• Change encoding: Encoding → Convert to UTF-8 fixes “’” garbage characters before importing.

Regex find and replace

Ctrl+H, set Search Mode to Regular expression:

New to regex? Read regex basics for office users.

A real clean-up, start to finish

Here is a typical bank export pasted into Notepad++, and the exact steps that turn it into something Excel will read without complaint.

1. Check the encoding first. If you see ₹ instead of ₹, go to Encoding → Convert to UTF-8. Doing this later means re-fixing everything.
2. Remove blank lines: Edit → Line Operations → Remove Empty Lines (Containing Blank characters).
3. Delete the report header with column mode: Alt+drag over the junk columns and press Delete.
4. Standardise dates with regex replace (Ctrl+H, Regular expression): find (\d{2})/(\d{2})/(\d{4}), replace with \3-\2-\1. Excel reads yyyy-mm-dd correctly regardless of regional settings.
5. Remove thousand separators inside numbers: find (\d),(\d{3}), replace \1\2 (run it twice for lakhs).
6. Turn multiple spaces into a tab so columns split cleanly: find {2,}, replace \t. Paste into Excel and each field lands in its own column.

Features people miss

• Multi-cursor typing: Ctrl+click in several places and type once.
• Compare two files: install the Compare plugin (Plugins → Plugins Admin) to see what changed between two exports.
• Record a macro for a clean-up you repeat monthly: Macro → Start Recording, do the steps, stop, save it with a shortcut.
• Find in Files (Ctrl+Shift+F) searches every file in a folder — perfect for “which of these 40 CSVs has invoice 1042?”.