Using JMP > Enter and Edit Your Data > Restructure Data > Create a New Column by Text Matching
Publication date: 04/12/2021

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 

Regular Expressions Editor

4. Below the Leftover box, click the Add button Image shown here.

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 Image shown here appears to the left of Cat.

8. Click the Add button Image shown here 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 

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.

Want more information? Have questions? Get answers in the JMP User Community (community.jmp.com).