You may well know that in Word you can change the case of your text very easily. What was originally all in lower case you can change to All upper or first character of each word to upper case and there are a few more. If you didn’t, then read the tip I did a while back on this subject. It is infuriating when you either type text into a spreadsheet, or receive a spreadsheet from someone else, that has text in the wrong case. There is a simple way to change this to what you need using the “LOWER”, “UPPER” or “PROPER” functions. It does mean that you can’t, as you can in Word, just highlight the cell and use a couple of keys, but you can still get the result you need without retyping the whole column of data. Unlike Word you can then autofill the formula to change the whole column or row of wrongly typed case, data.
Here is an example of wrong case data. 
- Select the cell to the right of this cell, or insert a new column if need be.
- From the Insert Function List locate and choose “LOWER”.
- Select the cell that has the wrong case data in it. In my example it is K10.
- Select OK and view the result.
The result looks like this. 
If you require the data to be all in capitals, use the “UPPER” function. 
The result will look like this.
We generally use the first character in Upper case and the rest of the word in Lower case when it comes to names. To get this effect we need to use the “PROPER” function.
Here is a list of names with exaggerated case mixture to show you that it really doesn’t matter what you receive, you can fix it. In this example we are going to have two new columns, as the names are in two separate columns in the data. 
- Select the cell to the right of the two names.
- Locate and use the “PROPER” function.
- Select the first cell of wrong case data. Here it would be A2.
The result will look like this.
You do not need to use the same function again in column D for the Surname. We will use the Autofill tool to copy and paste the formula into the cell D2.
- Now select C2:D2.
- Use the Autofill tool to fill in the data all the way down the column.
The result will look like this. 
You can use this inside another function (nest) to either pull both words into one cell, or split the two words into two cells. Yes, it’s a little bit more work but at least you don’t have re-type everything. The only problem in my example data is that Franck MacFall does not have an upper case “F” as he should have. But hopefully you will not have too many of these to change manually.
You could always create a few macros if this is something you have to do regularly!
