Create a New Column by Text Matching

The New Column by Text Matching option enables you to use regular expressions (which identify patterns in text) to match unstructured text in a column and then create a new column from the matched text. This feature is an alternative to using the Text Explorer platform to write regular expressions and then save the results to a column.

1. Select Help > Sample Data Library and open Pet Survey.jmp.

2. Select the Survey Response column.

3. Select Cols > Utilities > New Column By Text Matching.

Figure 4.34 Regular Expressions Editor 

4. Below the Leftover box, click the Add button .

5. Select Blank from the list and click OK.

6. Next to Title, enter Cat.

7. Next to Regex, enter (cat(?:s?))([\s\.]).

This expression finds “cat”, optionally followed by “s”, with either a space or period afterward.

Note: If the regular expression has an error, an X icon appears to the left of Cat.

8. Click the Add button again.

9. Select Blank from the list and click OK.

10. Next to Title, enter Dog.

11. Next to Regex, enter (dog(?:s?))([\s\.]).

12. (Optional) To save the script so that you do not have to repopulate the Regular Expressions Editor after closing and reopening it, select one of the Save Script options from the red triangle menu.

13. Click OK.

Figure 4.35 Data Table with New Columns 

The Use Result Specification option in the Regular Expressions Editor gives you control over which matched text appears in the new column:

• If you don’t select Use Result Specification (the default setting), the result in the new column is whatever is produced by the regular expression in the right side of the editor.

• If you select Use Result Specification, then the result in the new column is whatever is produced by the Result expression in the right side of the editor. If your regular expression is Air(port), then the new column contains the word port when found in the original column.

For more information about the Regular Expression Editor, see Customize Regex in the Regular Expression Editor in Basic Analysis.

To learn more about regular expressions, visit Regular-Expressions.info.