Replace Text Or Pattern
- 04 May 2021
- 1 Minute to read
-
Print
-
DarkLight
-
PDF
Replace Text Or Pattern
- Updated on 04 May 2021
- 1 Minute to read
-
Print
-
DarkLight
-
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
- Select ‘Cells’ from the ‘Replace’ drop down menu.
- Select the Column(s) that may contain the cells which need to be replaced.
- Input a pattern/text that needs to be replaced.
- Input text that intends to replace the field you wish to change.
- A preview is displayed on the grid.
- 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
- Replace missing’ replaces all missing cells in a column with a new value.
- Select ‘Missing from the ‘Replace’ drop down menu.
- 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.
- Select the appropriate columns.
- 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:
- Replace - Missing - Custom Value with a new value: Sample
Location | Location_new |
---|---|
Australia | Australia |
Sample | |
America | America |
Sample |
- Replace - Missing - Fill Down with last valid **value
Location | Location_new |
---|---|
Australia | Australia |
Australia | |
America | America |
America |
- Replace - Missing - Null**
Location | Location_new |
---|---|
Australia | Australia |
Null | |
America | America |
Null |
- Replace - Missing - Sum**
This should be applied for only integer data type columns.
Age | Age_new |
---|---|
10 | 10 |
30 | |
20 | 20 |
30 |