Scenario:
The customer uses a naming convention for their Customer IDs. They would like to change it by bulk. Instead of manually updating each record, they can use a formula to display the desired result, then export the search and then import it back. The formula column will be mapped to the Customer IDs column in during import.
Consider the list of Customer IDs below:
MNL-First Company
MNL-Second Company
EUR-Third Company
CZ-Fourth Company
MNL-Fifth Company
The customer would like to change MNL to PHP.
Solution:
Create a saved search that will display the records to be edited:
1. Navigate to Lists > Search > Saved Searches > New.
2. Choose Customer.
3. In the Criteria tab, set Name/ID contains MNL
4. In the Formula tab, set the following:
Field: Formula (Text)
Formula: REGEXP_REPLACE({entityID}, 'MNL', 'PHP')
Where: {entityID} = field to be evaluated
MNL = string to be searched for in all values entered in {entityID} field limited by the search filters
PHP = string to replace MNL
5. Click Save & Run.
6. Click Export - CSV.
The CSV file will display the following for the Company column:
PHP-First Company
PHP-Second Company
PHP-Fifth Company
7. Proceed with the CSV Import.