Automagic search and replace for spreadsheets

By Ben Gross
in

If you have ever received an a spreadsheet in email that you realize needs a series of repetitive changes that could take a mind numbing hour or more to fix, this new service might interest you. Magic/Replace from Dabble DB is billed as “Data Cleanup for Everyone” and the service does a pretty good job at just that.

The free online tool will let you do “automagic” search and replace operations on data in a spreadsheet. Effectively, the tool works by example. You import your tabular data (CSV, TSV or XLS) and then make changes any changes you want to a single row of data. You will then see those of changes propagated across a preview of your data. Click submit and it will transform all your data for you and send an email where you can download the new data set.

The service is compelling since it will let you easily make the kinds of transformations that can be a serious pain if you are not a regular expression expert. For example, Magic/Replace makes it easily to normalize, combine, reorder or split columns or reformat a column of phone numbers from (555)/5551212 to 555-555-1212. There is almost no user interface to speak of, which is impressive in itself. The site has a nice two-minute tutorial video that does a good job of explaining the features.

Published December 3, 2008