Find and Replace

When it comes to maintaining and processing huge amount of data, the major concern that needs to be addressed is data quality. The quality of the data often degrades over time. This could happen due to a number of reasons. Some of the prominent reasons could be,

  • Data being blended from multiple sources
  • Data being keyed in by different people
  • Non-standardization of the data stored

For instance, if you take a CRM database, data could be keyed in by several sales reps and each of them will have his/her own way of entering data. Over a period of time, this inconsistency will affect the quality of data. One such common case is, some reps might use the abbreviation for a country (CA for Canada) while others might prefer to spell out the complete word.    

Such variations will slowly corrupt the sanity of data and in turn, affect the accuracy of the analysis. Zoho Analytics allows you to clean up and standardize the data in your tables using the 'Find and Replace' option. This option will help you to locate a value in a column and replace it with another value. It is extremely useful when working with large sets of data that needs to be quickly cleaned up.  

Let us now see how we can use the Find and Replace option in Zoho Analytics for data cleansing.

The below fictitious dataset contains Stores Sales data from various countries across the world.

In the above snapshot, the names of the countries are listed in an inconsistent manner. Let us now see how we can use the Find and Replace option to standardize this data.

To use this,

  • Right-click on the Country column and select Find and Replace

​

  • In the Find and Replace dialog that opens, enter the relevant data for the Find and Replace fields. In this example, we are replacing 'CA' with 'Canada'.

​

  • Selecting the Bulk Update option will replace all the values in the column with the input given in the Replace with section. 
  • Selecting Do not replace null values (during bulk update) will replace all the values during the bulk update except those with null values in the cell.
  • Selecting Match Entire Cell will replace the contents of the cell only if the entire value in the cell matches with input given in the Find Section.
  • Once you select the necessary options, click Replace All.

The below snapshot shows the names of the countries after being cleansed using the Find and Replace option.

​

A few points to be noted while using Find and Replace:

  • Find & Replace operation is Case Sensitive.
  • Users can Find & Replace empty/null values by leaving the corresponding boxes empty. As in, if you wish to replace an empty cell with a value, leave the Find section Empty and enter the value you wish to replace the empty cell with, in the Replace With section and vice versa.
  • Do not enter symbols in Find & Replace fields for currency and percentage data types.
  • Find & Replace will be applied over filtered data if you have applied a Filter on the table.

Find and Replace for Date Datatype

In this section, we are going to see how to find and replace a date in the column. 

To find and replace a value in the date column,

  • Right-click on the Date column and select Find and Replace.

  • In the Find and Replace dialog that opens, select the date that you wish to find and replace. Enter the date to be found in the Find section and the date to be replaced in the Replace With section.
    In the below example: We will be finding the date 25 Oct 2017 00:00:00 and will be replacing it with 25 Nov 2017 14:00:40

Note

You can also replace individual components of the date. Let us assume that you would like to change the year 2019 to 2021. You can do so by entering the year in the corresponding Year section as shown in the snapshot below.

This will replace the dates with the year 2016 to 2017. The same can be done for any other components of the date as well. All that you will have to do is leave the other components empty as shown in the snapshot and enter the information only in the field that needs to be altered.

  • Selecting the Bulk Update (Replace all values in the column) option will replace all the values in the column with the date in the Replace with section.
  • Selecting Do not replace null values (during bulk update) will replace all the values during the bulk update except those with null values in the cell.
  • Once you select the necessary options, click Replace All.

​

A few points to be noted while using Find and Replace for Date datatype:

  • Hours provided should be in 24 hours format (0 - 23).
  • Find & Replace will be applied over the filtered data, if you have applied a Filter on the table.

Not just this, you can similarly find and replace data types such as numerical, URL, percentage, currency etc.