How to setup Data Formatting
This feature allows you to quickly modify data and transfer from Data Source to Data Destination what you need.
Pay attention to this!
After setup, the Data Formatting will create a NEW variable with the result of formatting. Transfer this NEW variable to the appropriate field in the Data Destination settings. After formatting, still have the option to transfer both the original data and the formatted NEW variable.
The following features are available in Data Formatting:
1. Case change - 4 variations of case change in a column.
2. Convert to a string from a list - allows you to change the display of values in the list in the field to display them as a string with the specified separator.
3. Convert to list - allows you to convert in string based on commas, space, etc.
4. Convert to number - allows you to convert different variations of numbers and letters into a single form of a number without letters.
5. Cut by regular expression - allows advanced users to use regular expressions.
6. Cut from-to - allows you to set the gap between the characters that you want to leave, and delete everything else.
7. Find and replace - classic search and replace with the desired value.
8. Find and replace in range (numbers) - you can specify the limit of numbers from and to, which will be replaced, for example, with words.
9. Format date/time - allows you to change the format to fit the requirements for CRM systems or other services.
10. Format the number (10’000) - allows you to change the representation of the number.
11. Leave first symbols - allows you to leave the specified number of characters from the beginning.
12. Leave the first words - allows you to leave the specified number of words from the beginning.
13. Leave the last symbols - allows you to leave the specified number of characters from the end.
14. Leave the last words - allows you to leave the specified number of words from the end.
15. Remove brackets and its contents - this allows you to remove brackets in three versions and the values that are in these brackets.
16. Remove everything after the symbol - deletes all values after the specified character.
17. Remove everything before the symbol - deletes all values up to the specified character.
18. Remove first symbols - deletes the specified number of characters, starting from the beginning.
19. Remove first word by position number - the ability to delete a word by specifying its number according to the number listed first.
20. Remove first words - allows you to set the number of words at the beginning that should be deleted.
21. Remove last symbols - removes all values to the specified symbol from the end.
22. Remove last words - removes all values after the specified character from the end.
23. Remove word from end by position number - the ability to delete a word by specifying its number according to the number since the end.
24. Replace - allows you to replace the values you are looking for and allows you to configure many different values in one Data Formatting.
25. Replace value by replacement list - allows you to replace the values specified in the list. Not one by one, but by a list.
26. Round the column - allows you to round numbers to an integer or to a certain number of decimal places.
27. Format phone - allows you to delete or add + to the phone number.
Let’s look at each function separately with examples.
Add a separate Data Formatting after setup the Data Source and before setup Data Destination.
To do this, click on "+" under the Data Source and select Data Formatting from the list.
Now you need to specify a field in the Data Source that will be formatted when transferred to the field Data Destination.
Next, select one of the functions in the list of actions.
Using the example of a Data Source in Google Sheets, we will change the column, depending on one or another action.
Note!
After setup the Data Formatting, a NEW variable will be created with the formatting result. Choose to transfer this NEW variable to the appropriate line in the system settings of the Data Destination, as you can pass both original data and already formatted as a NEW variable.
Work of all functions on examples
1. Case change
2. Convert to a string from a list
This function is only available for multiple fields.
3. Convert to list
4. Convert to number
5. Cut by regular expression
This feature is designed for advanced users and allows more flexibility to customize data formatting with special values.
For example, if the regular expression "Postal code [ d+]", then instead of "[ d+]" the value with variable value will be substituted because we do not know the postal code number beforehand.
For more information about regular expressions, see: https://www.regular-expressions.info
An example of using a function to get a phone number and email address from the body of an html email:
To cut out a phone number:
\+[0-9]{11,12}
To cut out an email address:
[a-z0-9]+@[a-z0-9]+\.[a-z]+
6. Cut from-to
7. Find and replace
8. Find and replace in range (numbers)
9. Format date/time
10. Format the number (10’000)
11. Leave first symbols
12. Leave the first words
13. Leave the last symbols
14. Leave the last words
15. Remove brackets and its contents
16. Remove everything after the symbol
17. Remove everything before the symbol
18. Remove first symbols
19. Remove first word by position number
20. Remove first words
21. Remove last symbols
22. Remove last words
23. Remove word from end by position number
24. Replace
25. Replace value by replacement list
26. Round the column
27. Format phone.
