Find and Replace

Find and Replace

Find and replace values in your column using the replace transform. You can find the value to replace by matching the value using different options available and replace it with your input.

To find and replace column data:

1. Right-click the column name and select Replace from the context menu. 

2. DataPrep offers several advanced matching options to make it easier to find a value from your column data. The options available under Replace options are:
  1. Start and end index
  2. Start index and length
  3. Matching text or pattern
  4. Regex
  5. First ’n’ characters
  6. Last ’n’ characters
3. Based on the selected option, provide your input to find the value and provide the value which should replace what is found.

Let's take a look at the inputs required for some of the options listed above. 

Start and end index: 
Start index - Replace the value starting from the start index. The default start index is 1.
End index - Replace the value till the end index.

Start index and length: 
Start index - Replace the value starting from the start index. The default start index is 1.
Length - Replace the value from the start index to the given length. 

Matching text or pattern: 
Text or pattern to match - Replace the value that matches with the given text or pattern.
Starting text or pattern -  Replace the value starting from the given text or pattern.
Ending text or pattern -  Replace the value ending before the given text or pattern.
Note: If you are not familiar with pattern matching in DataPrep, read about it here.

Regex pattern - Enter the regex of the value which you want to replace.

First 'n' characters: 
Number of characters to replace - Specify the number of characters to replace from the start of the value. 

Last 'n' characters:
Number of characters to replace - Specify the number of characters to replace from the end of the value.

Replace with:
The value you want to replace the matched text or pattern with.

4. You can also choose to Ignore case or Replace all if required.

Ignore case: Ignore case when matching the text or pattern to find the value. 
Replace all: Replace all matching occurrences of the input provided. If unchecked, the transform by default replaces the first matching occurrence as the result.

5. DataPrep shows a live preview of the changes made to the column.

6. You can apply this transform to multiple columns at the same time. Select the columns using the + icon under the Columns to apply section.

    Zoho DataPrep Personalized Demo

    If you'd like a personalized walk-through of our data preparation tool, please request a demo and we'll be happy to show you how to get the best out of Zoho DataPrep.

    Zoho CRM Training

      Create, share, and deliver

      beautiful slides from anywhere.

      Get Started Now

              Zoho CRM Training Programs

              Learn how to use the best tools for sales force automation and better customer engagement from Zoho's implementation specialists.

              Zoho CRM Training

                Zoho SalesIQ Resources

                    Zoho TeamInbox Resources

                              Zoho DataPrep Resources

                                Zoho DataPrep Demo

                                Get a personalized demo or POC

                                REGISTER NOW

                                  Design. Discuss. Deliver.

                                  Create visually engaging stories with Zoho Show.

                                  Get Started Now

                                                        • Related Articles

                                                        • Reload data

                                                          You can manually refresh your dataset with the latest data by reloading data from your data source.  You can find the option to reload data at two places in Zoho DataPrep.   1. The Reload data from source option from the Import menu.  ​ 2. The reload ...
                                                        • Export data to cloud databases

                                                          You can export data to the following cloud databases. Amazon RDS - MySQL Amazon RDS - MS SQL Server Amazon RDS - Oracle Amazon RDS - PostgreSQL Amazon RDS - Maria DB Amazon RDS - Amazon Aurora MySQL Amazon RDS - Amazon Aurora PostgreSQL Amazon ...
                                                        • Extract from text

                                                          Zoho DataPrep offers options to identify and extract a subset of the data from a column. You can extract very specific portions of the column data using the extract transform. For example, your column contains a mixture of letters and numbers, but ...
                                                        • Scheduling data import

                                                          You can schedule data import using the Schedule import option available for datasets in your project. You can choose to Schedule import for data sources: URLs, FTPs, Cloud storage, and databases. Click the Schedule import option from the Import menu ...
                                                        • Zoho CRM Data Solutions

                                                          Powered by Zoho DataPrep  Zoho DataPrep helps you clean, prepare, and warehouse your CRM sales data in more than one way. You can use DataPrep along with CRM solutions to perform data cleanup (remove duplicate records, find and replace empty records, ...



                                                        Watch comprehensive videos on features and other important topics that will help you master Zoho CRM.


                                                        Download free eBooks and access a range of topics to get deeper insight on successfully using Zoho CRM.


                                                        Sign up for our webinars and learn the Zoho CRM basics, from customization to sales force automation and more.

                                                        CRM Tips

                                                        Make the most of Zoho CRM with these useful tips.

                                                          Zoho Show Resources