Search and replace data

Search and replace data in Productsup.

A common use case is to search for keywords or parts of your data and replace them with another term. This could be a static replace where the same value should be searched and replaced for all products in the column, or a dynamic replace where the values differ per product.

Searching and replacing static text

You can add rules that search for and replace terms across all products in your column. The conditions are static and affect all products where the search term is found.

Note

If you are searching and replacing over 100 conditions, consider using a list instead. This is more efficient and allows your data to be processed more quickly. See List Feature: Normalization.

Replacing text with the text replace box

To search and replace the same values for all products in a column, use the text replace box:

  1. Add the text replace box

  2. Input the term you wish to search for

  3. Input the term you wish to replace by

  4. Choose whether your input is case sensitive or insensitive

    • sensitive: the capitalization is regarded when performing the search

    • insensitive: the capitalization has no effect

text_replace.png

So according to the above box, our data would be changed in this manner:

Before

After

In this example the word “exists” exists two times.

In this example the word “appears” appears two times.

You can also leave the replace by field empty in order to delete your search term. This may be useful if you want to delete whitespaces, for example.

For more information on how to remove whitespaces, see Clean and format your data.

Using a map-replace box in place of multiple text replaces

Instead of adding multiple text replace boxes, you can replace many things simultaneously by storing one rule per line in a single map-replace box.

  1. Add the map-replace box

  2. Write your search term into the input field

  3. Add the delimiter directly after your search term

  4. Write the replace term directly after the delimiter

  5. Choose whether your input is case sensitive or insensitive

    • sensitive: the capitalization is regarded when performing the search

    • insensitive: the capitalization has no effect

  6. You can also customize your delimiter

    • this would be done if your search or replace terms contain the delimiter itself

map_replace.png

So according to the above box, our data would be changed in this manner:

Before

After

Please note: you should customize the delimiter in your map-replace box if it appears in your search search or replace terminologies

It’s important to know that you should customize the delimiter in your map-replace box if it appears in your search or replace terms

Replacing the entire data for a column with the mapping box

The mapping is similar to the map-replace, but the entire data for a product for that column has to match the search term. In other words, an entire phrase, not just a single word, is replaced with the values you input.

  1. Add the map-replace box

  2. Write your search term into the input field

  3. Add your colon delimiter directly after your search term

  4. Write the replace term directly after the delimiter

  5. Choose whether your input is case sensitive or insensitive

    • sensitive: the capitalization is regarded when performing the search

    • insensitive: the capitalization has no effect

mapping.png

So according to the above box, the following data would be affected in this manner:

Before

After

blue

cyan

This product is blue

This product is blue

As you can see, the last example does not change as the entire attribute does not match our input.

Dynamically searching and replacing text based on a condition

You can add rules that search for and replace terms across all products in your column, based on a condition. The conditions are dynamic and affect only products where the condition is met and the search term is found.

Replacing dynamic values

With the replace dynamic value box you can replace a value taken from another column. For example, if your title includes the data from the brand column, you can replace this with another column or a static value. If the data from the brand column is not in your title, then nothing will be replaced.

  1. Add the replace dynamic values box

  2. Select your search column

  3. Choose a replace column or a static value to replace your search term with

  4. Select the match type

    • broad: the search term has to simply be included in the input in the search column

    • exact: the search term has to exactly match the entire input in the search column

replace_dynamic_value.png

So according to the above box, the following data would be affected in this manner if the availability is “in stock”:

Before

After

A great t-shirt, in stock

A great t-shirt, Get it now!

Replacing text if another column contains a term

With the replace if a column contains a box, you can search another column, and if there is a match then search and replace a value in your current column.

  1. Add the replace if a column contains a box

  2. Select your column in which to search for in the dropdown

  3. Input a word or phrase to search for in the previously-selected column, before adding the colon delimiter

  4. Type a word or phrase to search for in your current column should the above condition match, before adding the colon delimiter

  5. Input a word to replace the search term in the current column, if the search term is found

replace_if_column_contains.png

So according to the above box, if the description column contains “Planet Express”:

Before

After

Buy 5 Shipping Boxes designed for packaging your goods for shipping

Buy 5 Packaging Boxes designed for packaging your goods for shipping

Assigning a value if the input from two columns matches

You can check if two columns match and if this is the case, assign a value:

  1. Add the compare columns box

  2. Select column A and column B to compare with each other for a match

  3. If there is a match, input the replace mode and replace value under handle match

  4. If there is no match, input the replace mode and replace value under handle no match

  5. Select what the box should do if one of the columns does not exist

compare_columns.png

So if column A matches column B, then we will overwrite all data in the column where we added the box and assign the value “description=title!". If column A doesn’t match column B, we will not change the data in the column where we added the box.

Removing duplicate words

You can remove instances of duplicate words by using the remove duplicate words box

  1. Add the remove duplicate words box

  2. Input the delimiter for how to word boundaries should be recognized (standard is a space)

    • you may want to set a custom delimiter if you are searching for duplicate entries in a category taxonomy where the terms are separated by an arrow, for example

remove_duplicate_words.png

So according to the above box, our data would be changed in this manner:

Before

After

Cat Cat Dog Cat Parrot Tortoise

Cat Dog Parrot Tortoise

Replacing text based on a condition

You can replace text based on a condition relating to another column.

  1. Add the text replace if a column contains a box

  2. Select your source column

    • please note that the column must be in the stage before you are implementing this rule (so for a rule on intermediate you can select import columns)

  3. Add your condition using the dropdown to select between equals and contains, before inputting the condition text

  4. Input the search and replace terms

text_replace_if.png

So according to the above box, the following data would be affected if the product_type column for this product equals t-shirts:

Before

After

This is a Cotton t-shirt

This is a synthetic fibre t-shirt

Replace text in between two words/symbols

This box can be used to remove or replace value between two other values:

  1. Add the replace in between box

  2. Define a from and to

    • these values will also be removed when the replace is carried out

  3. Add a term to replace the search term with

replace_text_in_between.png

So according to the above box, the following data would be affected in this manner:

Before

After

This is a great t-shirt “designed by a world class designer” in blue

This is a great t-shirt in blue

Remove text in between brackets

The remove terms in brackets box are similar to the replace in between box and is used to remove values between two brackets:

  1. Add the remove terms in brackets box

replace_between_brackets.png

So according to the above box, the following data would be affected in this manner:

Before

After

This is a great t-shirt, the very best (endorsed by world-famous jazz saxophonist Joshua Redman)

This is a great t-shirt, the very best