Replace Text Or Pattern
  • 04 May 2021
  • 1 Minute to read
  • Dark
    Light
  • PDF

Replace Text Or Pattern

  • Dark
    Light
  • PDF

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 Category_new
accessories fashion
accessories fashion
accessories fashion
fruits fruits
vegetables vegetables

‘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
Location Location_new
Australia Australia
Sample
America America
Sample
  1. Replace - Missing - Fill Down with last valid **value
Location Location_new
Australia Australia
Australia
America America
America
  1. Replace - Missing - Null**
Location Location_new
Australia Australia
Null
America America
Null
  1. Replace - Missing - Sum**

This should be applied for only integer data type columns.

Age Age_new
10 10
30
20 20
30

What's Next