Data Cleaning & Formatting - Module 3 (Text to Columns Function)
- Duration: 2:51
- Updated: 14 Dec 2014
In our first module, we will be learning about text manipulation formulas. By the end of this module, you will be able to correctly enter text manipulation formulas and identify their cleaning functions in a data set when using Excel 2013 for PC.
Text Manipulation formulas can be used with both PC and Mac versions of excel. They automate the process of cleaning data by applying formatting formulas across multiple cells. Let’s review some of these key formulas:
● The “Trim” formula removes extra spaces.
● The “Proper” formula makes the first letter of every word an uppercase letter.
● The “Clean” formula removes all non-printable characters from the text.
● The “Upper” formula capitalizes all letters in the text.
● And the “Lower” formula makes all letters lower case.
Let’s view the following example to see how these formulas affect data.
You have just received a file from a co-worker on your team that contains employee information. It contains fields such as employee ID, name, hire date, salary, and location. However, some of the cells have issues, such as extra spaces, lower case first letters, non-printable characters, and entire words with lower and upper case letters.
In order to fix each of issues, you need to create columns to the right of the column you will be editing. Next, you need to type in the appropriate formula in the adjacent cell to make the changes you need to fix. For example, in cell C2, we would type “equals TRIM open parenthesis then select cell B2 and close parenthesis.
You would repeat this process with the other cells, typing in the appropriate formula for the issue you are trying to fix. For instance, in cell C4 you would use the “=Proper” formula…and in cell C10 you would type the “=lower” formula to make all words lower case.
So you see, text manipulation formulas allow you to make formatting changes in an automated way that saves you time and is more efficient.
This brings us to the end of Module 1.
http://wn.com/Data_Cleaning_&_Formatting_-_Module_3_(Text_to_Columns_Function)
In our first module, we will be learning about text manipulation formulas. By the end of this module, you will be able to correctly enter text manipulation formulas and identify their cleaning functions in a data set when using Excel 2013 for PC.
Text Manipulation formulas can be used with both PC and Mac versions of excel. They automate the process of cleaning data by applying formatting formulas across multiple cells. Let’s review some of these key formulas:
● The “Trim” formula removes extra spaces.
● The “Proper” formula makes the first letter of every word an uppercase letter.
● The “Clean” formula removes all non-printable characters from the text.
● The “Upper” formula capitalizes all letters in the text.
● And the “Lower” formula makes all letters lower case.
Let’s view the following example to see how these formulas affect data.
You have just received a file from a co-worker on your team that contains employee information. It contains fields such as employee ID, name, hire date, salary, and location. However, some of the cells have issues, such as extra spaces, lower case first letters, non-printable characters, and entire words with lower and upper case letters.
In order to fix each of issues, you need to create columns to the right of the column you will be editing. Next, you need to type in the appropriate formula in the adjacent cell to make the changes you need to fix. For example, in cell C2, we would type “equals TRIM open parenthesis then select cell B2 and close parenthesis.
You would repeat this process with the other cells, typing in the appropriate formula for the issue you are trying to fix. For instance, in cell C4 you would use the “=Proper” formula…and in cell C10 you would type the “=lower” formula to make all words lower case.
So you see, text manipulation formulas allow you to make formatting changes in an automated way that saves you time and is more efficient.
This brings us to the end of Module 1.
- published: 14 Dec 2014
- views: 7