Replace

<< Click to Display Table of Contents >>

Navigation:  Reference > Transforms >

Replace

Description

Replace text in one or more columns.

 

Examples

Replace US state initials in the 'Address' column:

 

how to replace multiple text example

 

Reformat phone numbers using a regular expression:

 

how to clean telephone number with regex example

 

Replace empty values:

 

How to replace empty values in a table example

 

Inputs

One.

 

Options

Check the column(s) you wish to transform.

Click the add-button@1x button to add a new filter criteria.

Click the arrow-up@1x button to move the selected terms up.

Click the arrow-down@1x button to move the selected terms down.

Click the remove-button@2x button to delete the selected filter terms.

Click the clipboard-arrow-right-circle_2@1x button to copy all terms to the clipboard.

Click the clipboard-arrow-right-circle@1x button to paste terms from the clipboard.

Click the cancel@1x button to clear all terms.

Add the terms you wish to replace and how you wish to replace them.

oChoose the Match type as:

Text to replace matches contained in the text.

Exact text to replace the text only if it matches exactly (whitespace sensitive).

Regex to match using a Regular expression.

Empty to match empty cells (cells with whitespace are not empty).

oIn Replace put the text you want to replace for Text, Exact text or Regex matching. You can use a column variable.

oIn With put the text you want to replace it with. You can use a column variable.

Check case sensitive to use case sensitive matching for Text, Exact text or Regex matching.

 

Notes

Comparisons are whitespace sensitive. You can use Whitespace to remove whitespace before replacing.

If you are pasting in replacement terms, the format expected is comma delimited text with 3 columns.

oThe first column is the 0-based index of the Match Type drop-down list.

oThe second column is the text Replace.

oThe third column is the text With.

For example, pasting in:

 

0,YES,1

2,^Y$,1

 

Will add:

 

replace-paste-example

 

You can also try copying terms to the clipboard and pasting into a text file to see the format. If you only paste in 1 column of text, it will be assumed to be a list of Replace. You can, of course, use Easy Data Transform to create the appropriate filter terms and paste them into memory.

 

See also

Insert

Substitute