# www.TestsTestsTests.com ## Microsoft Excel FREE Resources Index Page Free Excel Tests – Formulas to Manipulate and Clean Data – Excel 2010

# Formulas to Manipulate & Clean Data Excel Test

## Excel 2010 Training – Working with Data Free Online Microsoft Excel Test

### Formulas to Manipulate & Clean Data Excel Test 1) Study the screenshot above.  What formula can you use to change the case for the surnames in cells B2:B8 to match the case of the names in column A2:A8?
a) The PROPER function.
b) The UPPER function.
c) The LOWER function.
d) The INITIAL_CAPS function.

2) What is the purpose of the TRIM function?
a) It removes a specific number of characters from a text string.
b) It removes all non-printable characters.
c) It removes all spaces from a text string except single spaces between words.
d) None of the above options are correct. 3) Study the screenshot above.  What result will the formula in cell B34 return?
a) It is impossible to say.
b) It will return:  CYP278.
c) It will return CYP278632 with all extra spaces removed.
d) It will return 278632.

4) Which of the following is the name of the function that changes text into all capital letters?
a) The NUMCAP function
b) The UPPER function.
c) The ALLCAP function.
d) The CAPITALIZE function.

5) If the text in cell A1 contains around 2 blank spaces ahead of the word, which of the following formulas, when entered into a blank cell, represent the correct syntax for a formula to remove the blank spaces?
a) =TRIM(A1)
b) =TRIM(A1, 2)
c) =TRIM(2, A1)
d) =TRIM() 6) Study the screenshot above.  How can you extract only the initial (first letter of each person’s name) into the Initial column?
a) Enter the formula:  =LEFT(C2,1) in cell B2 and copy it down.
b) Enter the formula:  =RIGHT(C2,1) in cell B2 and copy it down.
c) Enter the formula:  =LEFT(C2:C8, 1) in cell B2 and copy it down.
d) Enter the formula:  =PROPER(C2, 1) in cell B2 and copy it down.

7) Which of the words below represent the format of text that had the LOWER formula applied to it?
a) Apples
b) APPLES
c) apples
d) aPpLeS 8) Study the screenshot above.  In column B we used the UPPER formula to change the names to all capital letters.  Column A is therefore no longer needed.  What will happen if you delete column A?
a) All the names in column B will disappear and an error message:  #REF! will appear.
b) All the names in column B will change back to PROPER case (first letter capitalized).
c) All the names in column B will now appear in column A.
d) Nothing will happen.

9) What is the purpose of the num_characters variable in the LEFT and RIGHT function?
a) It counts the total number of characters in a text string.
b) It allows the user to specify how many characters to the right or left of a word to extract.
c) It counts how many characters in a cell are numbers.
d) None of the options listed above are correct.

10) To clean what looks like two extra spaces within cell A2 of a worksheet, which of the following TRIM functions are correct?
a) =TRIM(2,2)
b) =TRIM(A2)
c) =TRIM(A2:A2)
d) All of the above are valid variations of the TRIM function.