Fast and lazy replacement in Excel

During every-day data wrangling, I often have to find and replace elements in Excel. This particularly happens when I open CSV and text files generated with R. Because French locales (and many others) use a comma as a decimal mark, Excel doesn’t recognise it in such files on my French-set system, and considers these values as text. Unfortunately, this prevents me from making quick checks with formulas…

Now, the find-and-replace utility of Excel is simple and efficient—nothing more than a (data) scientist wants—but it’s hidden somewhere in the menu options, or, even worse in the Mac version, in the GUI. As always with any repetitive and boring task, I had to make an AppleScript for it. I can’t help it. And here it is:

tell application "Microsoft Excel" to replace used range of active sheet what "." replacement ","

For once, I was fancy having it on a single line 🙂

Posted by Benjamin

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.