Data Cleaning & Formatting - Module 2 (Combining Text Manipulation Formulas)

In our second module, we will be learning how to combine text manipulation formulas when cleaning data on Excel. By the end of this module, you will be able to combine and implement text manipulation formulas in data cleaning when using Excel 2013 for PC.

Combining text manipulation formulas is useful when organizing and cleaning excel files. In our last module, we learned how to use one text manipulation formula at a time to clean data. What if your data has two issues in the same cell? You could fix the first issue, and then fixt the second issue separately -- or we could make this process a lot more efficient by combining the two formulas.

Let’s view the following example to see how these formula combinations affect data.

Using a revised version of the data we saw in Module 1, we will now clean our data using combined text manipulations.

Let’s begin by looking at cell B2. Notice how the name Burgess, Rochelle has an uppercase letter G in Burgess and an uppercase H in Rochelle. To fix this issue we need to apply the “=LOWER” formula to make all letters lowercase. Also notice that the first letter of each name - that is the B in Burgess and the R in Rochelle are lower case. We need to make these first letters upper case. To do this, we would use the “=PROPER” formula to make the first letter of each name uppercase.

Let’s add some extra columns to have a clean working area. We will be working to the right of the data we are manipulating. In cell C2, we would type the formula “equals PROPER open parentheses LOWER open parentheses B2 close parentheses close parentheses.” This formula will fix the capitalization and first letter lower case issues we found.

To apply this formula to the entire column, we will select this cell (click control + C) and highlight all of the other cells that we want to manipulate. We will now utilize the PC command “Alt+E+S+V” to paste this formula to all the desired cells.

A pop up will appear on your screen. Be sure to select the paste “formulas” option. Column C should now contain the “clean” version of the original data from column B.

In order to prevent future errors, we will need copy data in row C and paste it as a value rather than as a formula. For example, if we were to delete column B, our newly cleaned names column would create an error in the data.

So to avoid data errors, we will paste the newly cleaned data in column C as a value.

To do this select all of the data in column C (click control + C), press “Alt+E+S+V” and be sure to click on the “values” option.
This concludes Module 2 of Combination Text Manipulation Formulas.