Magic/Replace - an interesting 'show by example' way to clean up data

Magic/Replace looks pretty good – going by their screencast demo (2 mins). It allows you to simply and intuitively clean up spreadsheet data.

Once you've pasted (or uploaded) your data into their webform, you can make changes to a cell, and the system will intelligently apply the same kind of change to all the other cells in the same column.

For example, if you changed a cell containing the phone number 0987123532 by putting in some spaces so it became 0987 123 532, the system will put the same spaces into all of the other phone numbers.

It can handle quite complicated changes, like combining three separate fields ‘firstname’, ‘middlename’, ‘surname’ into a single name field with the format ‘surname, firstname middle-initial’. See the screencast for some other examples.

It looks like the system is available to use for free - you can try it out now. I haven't played with it much, so I don't know how well it works in general, but I think that at the very least they’re onto something with the way the system works – the principle behind it.

It’s great that the user doesn’t have to explicitly describe the changes they want to make – like by writing some sort of code. They can just do it; and the system can work out the analogous changes for the other instances of that field. I say ‘analogous changes’ because the other instances of the field will contain different data, so the system has to look deeper than just the superficial details of the changes the user made.

(Douglas Hofstadter argues that the ability to make analogies is a central aspect of intelligence. He’s got a book on this Fluid Concepts and Creative Analogies. I haven’t read it, but the idea that ability to analogise is important sounds sensible to me).

.

Discussion of the tool on Hacker News here.