Replace Text Or Pattern

Replace

Replaces cell value with another cell value. This is replicated across other cells in selected columns if the ‘find’ value matches the value in different cells.

Replace Cells

1. Select ‘Cells’ from the ‘Replace’ drop down menu.
2. Select the Column(s) that may contain the cells which need to be replaced.
3. Input a pattern/text that needs to be replaced.
4. Input text that intends to replace the field you wish to change.
5. A preview is displayed on the grid.
6. All matching cell values will be replaced after adding the rule.

For example – Replacing the term ‘accessories’ to ‘fashion’ in the column ‘category’.

‘Category_new’ will replace ‘Category’ once you click on ‘add’ this is an example of a preview.

Replace Missing

1. Replace missing’ replaces all missing cells in a column with a new value.
2. Select ‘Missing from the ‘Replace’ drop down menu.
3. Select the Column selection type i.e., Multiple or Range. Multiple stands for selecting one or many individual columns while range will select all the columns within a specified range.
4. Select the appropriate columns.
5. Select what it needs to be replaced with.

These are the available options:

• Custom value – Any value of your choosing will be replicated across all empty cells.
• Fill down with last valid value – Selects the last value in the column and replicates across all empty cells.
• Null – The value ‘Null’ will be replicated across all empty cells.
• Average – The average of all numbers in the selected column(s) will be replicated across all empty cells.
• Mode – The value ‘Mode’ will be replicated across all empty cells.
• Sum - The sum of all numbers in the selected column(s) will be replicated across all empty sells.

Here are some examples:

1. Replace - Missing - Custom Value with a new value: Sample

2. Replace - Missing - Fill Down with last valid **value

3. Replace - Missing - Null**

4. Replace - Missing - Sum**

This should be applied for only integer data type columns.