In a typical organization, most databases are built with data keyed in by different people. For example, a CRM database will be updated by several sales reps and each of these reps will have his/her own way of entering data. It can be ‘Road’ for some, while others may prefer ‘Rd’. Similarly, it can be Drive or Dr, and CA or California. Such variations slowly corrupt the sanity of the data and can make it unsuitable for reliable analysis & reporting.

The situation is made all the more worse if the data is aggregated from multiple data sources where each system follows its own conventions and processes. To make such data uniform and credible, periodic data cleansing becomes necessary. Only clean, sanitized data can provide you reliable inferences when analyzed.

Zoho Reports now supports a Find & Replace function which makes data
cleansing easy and which results in keeping your data clean. For using this function, right-click on a column name and choose the Find and Replace option. (you can also invoke the Find and Replace option under the More Options menu too).

Screen Shot 2016-09-22 at 6.57.05 pm

Enter the relevant data for Find what and Replace with fields.

Screen Shot 2016-09-22 at 6.58.19 pm

All the matching data in the column will get replaced.

Screen Shot 2016-09-22 at 7.05.16 pm

Other than with text values as shown above, the Find and Replace also works with date & numerical values. Say a sales rep mistakenly entered data with the year as 2007 instead of 2008 (a pretty common data entry error, especially when a new year arrives), you can easily cleanse such data too.

Screen Shot 2016-09-22 at 6.57.18 pm

Hope you like the Find and Replace function useful for your data cleansing job. Try it now in Zoho Reports & let us know your feedback.

  1. Arvind

    Themmai: Nice requirement. It’s not possible as of now but support for wildcard / regex is in our list of to dos.

  2. Arvind

    Themmai: Nice requirement. It’s not possible as of now but support for wildcard / regex is in our list of to dos.

  3. themmai

    is it also possible to find and replace keeping existing data? I need to append a small string to a load of columns and would like to use a wildcard like structure.(ie, add “now” to each field in a column)

  4. themmai

    is it also possible to find and replace keeping existing data? I need to append a small string to a load of columns and would like to use a wildcard like structure.(ie, add “now” to each field in a column)

  5. Clarence

    Hi Dennis,”Find and Replace” operation can be executed only on the data in your tables. It’s not possible on the reports created. In short, you can do a “Find and Replace” only on the underlying data residing in the table.Once the data gets modified by this operation, all the reports will have the latest changes reflected.Thanks,
    Clarence

  6. Clarence

    Hi Dennis,”Find and Replace” operation can be executed only on the data in your tables. It’s not possible on the reports created. In short, you can do a “Find and Replace” only on the underlying data residing in the table.Once the data gets modified by this operation, all the reports will have the latest changes reflected.Thanks,
    Clarence

  7. dahowlett

    Is this only affecting reports or also underlying data?

  8. dahowlett

    Is this only affecting reports or also underlying data?