facebook twitter pinterest google plus Youtube
www.TestsTestsTests.com

www.TestsTestsTests.com

Free Excel Tutorial – Formulas to Manipulate and Clean Data – Excel 2010

Formulas to Manipulate & Clean Data Excel Tutorial

Excel 2010 Training – Working with Data
Free Online Microsoft Excel Tutorial

* Using UPPER, LOWER & PROPER to Change Text Case
* Using TRIM to Remove Extra Spaces
* Using LEFT & RIGHT Function to Extract Characters


Text is not always in exactly the right format that you need it. Sometimes you need to change the case of words to upper or lower case or remove extra spaces or characters. Doing this manually can take up a lot of time and can lead to data entry errors. Formulas allow you to perform the above functions in record time.

Test your Excel skills with the corresponding FREE Online Multiple Choice
Using Formulas to Manipulate & Clean Data Excel Test



* Using UPPER, LOWER & PROPER to Change Text Case

New, and sometimes even seasoned, users of Excel at some point find themselves searching for the familiar Change Case button that is found in other Microsoft Office Application.  There is no such button in Excel and instead, to change the case of text, you use a formula. 

The case of a word refers to whether it is in all uppercase letters, for example HELLO, lowercase letters, for example hello or with the first letter as a capital and the rest of the word in lowercase, for example Hello.


* The UPPER Formula

Study the screenshot below:

Using UPPER, LOWER and PROPER to Change Text Case Excel Tutorial

To change the text contained in the Name column to be in all capital letters, we use the UPPER formula:

1.  Insert a blank column next to the column containing the text you wish to change to uppercase.   

2.  In the cell adjacent to the first word (name in the example above), type an equal sign (=) followed by the word UPPER and press the Tab key on your keyboard.

3.  After the bracket, select the cell you wish to change to uppercase or type the cell reference.

4.  Close the brackets and press Enter.

Your completed formula should look like this (prior to pressing the Enter key):

Using the UPPER formula Excel Tutorial

5.  You can copy the formula down to the cells below it by clicking and dragging the Fill Handle in the bottom right-hand corner of the cell.




* The LOWER Formula

We use the LOWER formula to change all the letters in a word to lowercase:

1.  Insert a blank column next to the column containing the text you wish to change to lowercase.  

2.  In the cell adjacent to the first word, type an equal sign (=) and then the word LOWER and press the Tab key on your keyboard.

3.  After the bracket, select the cell you wish to change to lowercase or type the cell reference.

4.  Close the brackets and press Enter.

Your completed formula should look like this (prior to pressing the Enter key):
Using the LOWER formula Excel Tutorial

5.  You can copy the formula down to the cells below it by clicking and dragging the Fill Handle in the bottom right-hand corner of the cell.




* The PROPER Formula

The PROPER formula allows you to capitalize the first letter of a word that has been entered either in all uppercase or all lowercase.  To use this formula:

1.  Insert a blank column next to the column containing the text you wish to change to be formatted with an initial capital letter.

2.  In the cell adjacent to the first word, type an equal sign (=) and then the word PROPER and press the Tab key on your keyboard.

3.  After the bracket, select the cell you wish to change to initial caps or type the cell reference.

4.  Close the brackets and press Enter.

Your completed formula should look like this (prior to pressing the Enter key):

Using the PROPER formula Excel Tutorial

 

5.  You can copy the formula down to the cells below it by clicking and dragging the Fill Handle in the bottom right-hand corner of the cell.

*IMPORTANT The cell containing the result of the UPPER, LOWER or PROPER formula is read by Excel as a formula and not as text.  If you delete the column containing the reference text (Surname in the example above), the formula will return an error.  To avoid this happening, after completing the formula and copying it down, copy the results of the formula and paste it over the original text or in a new column as Values Only (an option from the Paste menu).



* Using TRIM to Remove Extra Spaces

Data in Excel is often copied from another source.  For example, you may have copied a list from MS Word or the Internet.  This data can often contain extra spaces not only doesn’t look great, but could cause formulas, functions and data validation to not work correctly.

Sometimes the extra spaces may not be apparent, but when you try running a formula, it quickly rears its ugly head.

Study the screenshot below:

Using TRIM to Remove Extra Spaces Excel Tutorial

You will note that there are extra spaces in front of the words in the Department column.  This is easy to spot, but remember spaces at the end of a word or number, may not be visible but will still cause a formula to not work correctly.

To remove all extra spaces from text we use the TRIM function:

1.  Insert a blank column next to the column containing the data you wish to remove all extra blank spaces from.

2.  In the cell adjacent to the first entry, type an equal sign (=) and then the word TRIM and press the Tab key on your keyboard.

3.  After the bracket, select the first cell you wish to remove spaces from or type the cell reference.

4.  Close the brackets and press Enter.

Your completed formula should look like this (prior to pressing the Enter key):

Using TRIM to Remove Extra Spaces Excel Tutorial

5.  You can copy the formula down to the cells below it by clicking and dragging the Fill Handle in the bottom right-hand corner of the cell.

*IMPORTANTThe cell containing the result of the TRIM formula is read by Excel as a formula and not as text.  If you delete the column containing the reference text (Department in the example above), the formula will return an error.  To avoid this happening, after completing the formula, copy the results of the formula and paste it over the original text or in a new column as Values Only (an option from the Paste menu).


* Using LEFT and RIGHT Function to Extract Characters

The LEFT and RIGHT function in Excel returns either a set number of characters to the left of an entry or the right thereof.  It can be used on its own to quickly shorten words, provide initials or to clean unwanted characters at the beginning or end of a word.

* Using the LEFT Function

Study the screenshot below.  It is a worksheet that requires initial of each person to be entered into a column with the heading Initial.  I once watched a colleague manually type the initials for each person into a worksheet similar to this for literally hundreds of entries.  The formula described below could have done what took him hours to do, in a couple of seconds.

Using LEFT and RIGHT to Extract Characters Excel Tutorial

Using the LEFT function, you can quickly get Excel to extract characters from the left side of any data string. 

To use the LEFT function:

1.  Click in the first cell where you wish the extracted character(s) to appear.  In our example above, it would be in cell B2.

2.  Type an equals sign (=) followed by the word LEFT and press the Tab key on your keyboard.

3.  After the bracket, select the first cell containing the text or numbers you wish to extract characters from.  In our example above, this would be cell C2.

4.  Type a comma followed by the number of characters you wish to extract.  In our example we only want the first letter of the name, so the num_characters value will be 1.

5. Close the brackets and press the Enter key to accept the formula.

Your completed formula, prior to pressing enter, should look like this:

Using LEFT function Excel Tutorial

6.  You can copy the formula down to the cells below it by clicking and dragging the Fill Handle in the bottom right-hand corner of the cell.

NOTE: The RIGHT function functions in exactly the same as the LEFT function, but returns the letters from the right of a data string instead of the left. 



* Using the RIGHT Function

Study the screenshot below.  A manager needs only the numbers in each of the key fob codes in the KeyFob column.  The RIGHT function can be used to extract the characters at the end of each entry.

Using LEFT and RIGHT to Extract Characters Excel Tutorial

To use the RIGHT function to extract the characters on the right-hand side of a data string:

1.  Click in the first cell where you wish the extracted character(s) to appear.  In our example above, it would be in cell F2.

2.  Type an equals sign (=) followed by the word RIGHT and press the Tab key on your keyboard.

3.  After the bracket, select the first cell containing the text or numbers you wish to extract characters from.  In our example above, this would be cell E2.

4.  Type a comma followed by the number of characters you wish to extract.  In our example we want the last four characters, so the num_characters value will be 4.

5. Close the brackets and press the Enter key to accept the formula.

Your completed formula, prior to pressing enter, should look like this:

Using RIGHT function Excel Tutorial


6.  You can copy the formula down to the cells below it by clicking and dragging
the Fill Handle in the bottom right-hand corner of the cell.

IMPORTANT The cell containing the result of the LEFT and RIGHT formula is read by Excel as a formula and not as text.  If you delete the column containing the reference text, the formula will return an error.  To avoid this happening, after completing the formula, copy the results of the formula and paste it over the original text or in a new column as Values Only (an option from the Paste menu).


Now you have done the tutorial…

Test your Excel skills with the corresponding FREE Online Multiple Choice
Using Formulas to Manipulate & Clean Data Excel Test