Navigation: Reference > Transforms >
Replace text in one or more columns.
Replace US state initials in the 'Address' column:
Reformat phone numbers using a regular expression:
Replace empty values:
•Check the column(s) you wish to transform.
•Click the button to add a new filter criteria.
•Click the button to move the selected terms up.
•Click the button to move the selected terms down.
•Click the button to delete the selected filter terms.
•Click the button to copy all terms to the clipboard.
•Click the button to paste terms from the clipboard.
•Click the 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). The Replace value is ignored.
▪Letters to match letters, e.g. 'a'. The Replace value is ignored.
▪Digits to match numeric digits, e.g. '1'. The Replace value is ignored.
▪Punctuation to match punctuation characters e.g. ','. The Replace value is ignored.
▪Symbols to match symbol characters e.g. '$'. The Replace value is ignored.
▪Space to match white space characters. The Replace value is ignored.
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.
•If you can to keep the original column use Copy Cols to copy the column first.
•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:
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.