Cleaning up a text column with the calculator
In the dataset shown in the following screenshot, volunteers are listed with their first and last names:
Throughout the column, there are errors in the capitalization of names. We will apply the correct case to the names and separate the names into new columns for first and last name.
Getting ready
Enter the names in the screenshot into a new column in Minitab. Name the column Volunteer.
How to do it…
The following instructions will use the proper word commands in the calculator to create a column of correctly capitalized surnames:
- In the Calc menu, click on Calculator.
- In the section Store result in variable, create a new column called
Surname. - Filter the function list by selecting the text functions from the drop-down list. Then look for the function
Proper. Double-click on the function to move it into the calculator.
- Next, find the function
Wordin the text function list. Double-click on this to highlight and replace the sectiontextin the calculator. This should look like the following screenshot:
- Double-click on the column
Volunteerto bring it into the expression and replace the section labeled text. - Replace
word_numwith2and delete the delimiters and brackets. The expression should look like the following screenshot:
- Click on OK to create a column of surnames capitalized correctly.
How it works…
The text function Proper returns a capital letter at the start of each word. The rest are set to lowercase. The Word function is used to locate a word in a text string. The Word 2 function finds the second word in a cell, using spaces as delimiters by default.
Nesting the Word function inside the Proper command tells Minitab to find the second word for the text in the column Volunteer and then set the first letter in each word as a capital and all others as lowercase.
There's more…
There is a list of calculator functions within the Help menu for the calculator. The text functions include a number of useful tools. Note, Item is similar to the Word function but allows the separator to be specified. For example, A_123_tx could be used with an Item function such as Item(c1,2,"_") to define the separator of the items as an underscore.
It is also worth noting that Find and Replace work in the worksheet as they do in other applications. Press Ctrl + F, and Ctrl + H or they can be found in the Editor menu.