I batch-paste addresses from vendor portals into Excel and lose time fixing casing, stray line breaks, and extra spaces; I’m bouncing between Notepad++ (regex to collapse spaces), Excel TRIM/CLEAN, and a quick Power Query step. Is there a faster one-click tool or macro that cleans on paste in Windows/Excel 365 so I can shave minutes off each batch, ideally handling weird Unicode and diacritics?
ClipboardFusion did it for me: I bound a Trigger to Excel so Ctrl+Shift+V runs Text Scrubbing (trim, collapse multiple spaces, strip stray line breaks) plus a tiny C# macro that does string.Normalize(FormKC) to handle the weird Unicode, then pastes. It replaced my Notepad++ regex + TRIM/CLEAN + Power Query shuffle and turned each batch into a single hotkey. Macros need Pro, but the time saved paid for it fast.
AutoHotkey v2 did it for me: I bound Ctrl+Shift+V to Excel only, it grabs the clipboard, swaps NBSP/odd Unicode spaces, collapses doubles, strips stray line breaks, and TitleCases the address — like a lint roller for text. It’s about 15 lines and shaved a couple minutes per batch. If you don’t want scripting, PureText is fine for plain paste but it won’t fix spacing/casing.
@OP I pinned a “PasteClean” macro in Personal.xlsb to the QAT (Alt+1) that grabs the clipboard, swaps NBSP/odd Unicode spaces, collapses multiples via regex, runs CLEAN/TRIM (+ optional PROPER), then pastes — one click inside Excel. “If you don’t want scripting, PureText is fine for plain paste but it won’t fix spacing/casing.” Agreed; tiny gotcha: add the VBScript RegExp reference or use late binding.