Microsoft overuses the term ‘exciting’ to describe their latest work but the new ‘Transform by Example’ for Excel looks genuinely exciting.
However you get data for Excel, the common problem is ‘massaging’ it to fit your needs. Dates need changing to match Excel, addresses need splitting into address/state/zip and names separated into first/middle/last.
Excel’s Get and Transform is better at grabbing data from the Internet and some transformations into workable cells. But it’s clumsy and difficult with more complex tasks.
Excel Flash Fill, but better
Transform by Example can convert data based on some examples you give it.
In a way, it’s a big expansion of Excel’s current Flash Fill feature but it goes beyond that with supplied extensions for common situations and contributions from other users.
Ahem … Real Time with Excel is a step-by-step guide to bringing the latest real world information into Excel for your calculations. Also into Word or Powerpoint. Global stock and share prices, currency exchange rates, cyber currency rates even weather information. Includes working Excel files with the transformations all done for you.
In the Garage
Before you get too interested in Transform by Example for Excel … it’s a Microsoft Garage project at this stage. That means it’s still a work in progress, not even ready for the Office Fast Track beta testing.
That said, we like what it promises. Try it and give feedback, good or bad. That feedback helps improve the feature and convince management that it’s worth adding to Excel permanently.
Addresses Transformed
Here’s Microsoft’s example, which is worth looking at closely.
The addresses in Column E are a mess. Missing data (eg rows 7, 9, 11 and 14 lack a ZIP code) or too much (rows 5 and 15 have ‘USA’). That makes it hard for a text parsing formula or even Excel’s Flash Fill to work.
The Transform by Example in column F has not only pulled out the necessary info but also added missing elements. For example, row 8 has the city and state added.
It’s done by using the Bing Maps function to figure out an address from what people type.
Of course, you could have put the city, state and zip into separate columns. Or use Flash Fill to separate the single column of comma separated data into three columns.
If the Transform by Example can improve the conversion of date/time data into Excel, that alone will justify the new feature.
If you struggle with data massaging, give Transform by Example a try. Be patient with this early trial.
Download from the Microsoft Garage site.
