Recently, I had some databases sent in from a client that needed uploading to their CRM system. The problem is that that data wasn’t very tidy, and it needed some formatting before I could upload it. One of the issues was that some of the data was in upper case, others in lower case and some in sentence case! This is not really very useful!
In Microsoft Word you have a formatting option to convert text case, but this option is not available in Excel. Did you know there is an easy way to use a formula to convert text from upper to lower case? It uses the formulas UPPER, LOWER and PROPER
Here’s how to do it…
First you will need to add in an extra column to house the newly formatted data and the data should be in a text format.
We then use the following formulas in each cell that we want to change the case of the text. If you have a whole list of data to change, you can enter your formula in the first cell, and then copy it down to the subsequent cells using the fill handle.
In the example below, the original data is in column A, and our new data is in column B
Changing text to upper case
Assuming our data is in cell A2, to change it to upper case, in cell B2 we enter:
Changing text to lower case
Assuming our data is in cell A3, to change the data to lower case, use the formula
Changing text to sentence case
And finally, assuming our data is in cell A4, to change the data to sentence case (i.e. capitalise the first letter and the rest lower case) use the formula:
You will then see the newly formatted data in column B. To copy the formulas to adjacent cells, simply click the cell the that contains your formula and hover over the bottom right hand corner. You will see a small black cross hair. Left click and drag down to fill the subsequent cells. E voila!
Note: If you are then uploading your database to somewhere else, or wish to delete the original column of data, you will need to change your new formatted text to values only, rather than formulas. There are a few ways of doing this, but I find the simplest way of doing it is to copy the cells or cells and then paste them into the same location as values only.
To do this highlight your cells, right click and select copy.
Next, select the destination cell you wish to paste into then right click and select paste special > paste values
Your data is then ready to go!