4 ms·
Excel can be very useful for cleaning up dirty data, once you are familiar with it. I don't know whether Visual Basic for Applications (VBA), or whatever has r
by pasbesoin 17y ago
Excel can be very useful for cleaning up dirty data, once you are familiar with it. I don't know whether Visual Basic for Applications (VBA), or whatever has replaced it, currently supports regular expressions, but back around 1999 or 2000 defining a reference to Windows Scripting Host (WSH) provided that functionality. Very useful for making and extracting pattern matches; in my case, I needed to identify particular codes within a large, freeform text field that multiple people at multiple facilities filled in each in their own fashion.
Excel does have plenty of quirks, which can be rather aggravating. My un-favorite: Applying a text formatting function to something like 2334d2 resulted in 233400 rather than 2334D2 because the formatting function was hard-wired to recognize "d[number]" as specifying an exponent even when your formula or code was applying text formatting. (Which interpretation apparently, as I was eventually told by someone with experience in the area, comes from Fortran. The same thing happened with "e"; at least "e[exponent]" I was familiar with; "d..." caught me by surprise).
So, some bozo or bozos send you a lot of semi-regular data. You shove it into Excel, where some of your cleanup is automated, and Excel makes it very quick to navigate around and manually fix the rest.
BTW, I've not used OO Calc for anything significant, but I continue to read here and there that for serious spreadsheeting, it still cannot compare to Excel.