Extract from text

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 you only need the letters. A text column with the value "ABC123" can be extracted to a new column with the value "ABC". You could also extract "123" or "BC12" or any other combination to a new column.

DataPrep offers the following options identify and extract from a column: 
  1. Start and end index
  2. Start index and length
  3. Matching text or pattern
  4. Numbers
  5. Regex
  6. First’n’ characters
  7. Last ’n’ characters
  8. Valid values
  9. Invalid values
  10. Email
  11. URL
Here are more details for some of the options listed above. 

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

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

Matching text or pattern: 
Text or pattern to match - Extract the value that matches with the given text or pattern.
Starting text or pattern -  Extract the value starting from the given text or pattern.
Ending text or pattern -  Extract 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 extract.

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

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

Extract the username, domain, or both from an email column. 

Extract the domain of the URL, port, path used, the query parameters and more.

To extract data from a column:

1. Right-click the text column and select the Extract option from the context menu. 

2. Give a name under the Base column name field to update the new column name.

3. Choose one of the Extract options and provide the inputs required to extract specific portion of the value from the selected column.

4. You can also choose to store the extracted value in a 'column' or as a 'list' using the Store output as option.

Ignore case: Ignore case when matching text or pattern.
Number of matches to extract: Specify the number of matches to be extracted as columns. The default number is 1.

5. 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

                                                        • Extract from date

                                                          DataPrep provides various options to extract part of date values into a new column. The portions which can be extracted are: Year Quarter and year Month and year Week and year Date Time Quarter Month Week Day of the week Day of the month To extract ...
                                                        • Create buckets - Text

                                                          You can choose to implement bucketing in a text column using conditions. Consider a situation where you need to assign people to specific teams. You can implement the logic of adding values to a bucket, which in this case is the team name.  Name Team ...
                                                        • Extract from List and Map

                                                          DataPrep supports map and list data types. A Map is also known as the Object data type and the List is also known as an Array. DataPrep offers extraction of data from the list and map columns using the options: Extract from List Extract from Map To ...
                                                        • Text and pattern matching

                                                          Zoho DataPrep supports the following text and pattern matching types.  Text literals: Text literals are used to match the exact specified text in your data. Written using single quotes or double quotes ( '...' or "..." ). Regular expressions: Regex ...
                                                        • 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 ...



                                                        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