If you have ever been faced with text fields that contain poor formatting or capitalisation then you will find this guide on how to convert text fields to different cases a useful read…
Convert text case with Excel 2010
Data comes in many formats, sometimes the correct format; other times a seemingly random format. Text fields in particular can cause issues because they are more likely to allow a free-format data entry; hence they are susceptible to user data entry errors.
A number of times I have been working with data from external sources and been faced with text entries that are just too randomly formatted to be of any use. This is when knowing how to convert the text fields becomes very useful.
There are 3 ways in which you can choose to convert a text field; all have a use depending on how you wish to display your data.
Convert the text into CAPITALS
To convert the text field into all capital letters then you need to use the UPPER function. The UPPER function takes the format:
Where text can refer to either a typed word, like =UPPER(“dedicatedexcel”) which would return the result DEDICATEDEXCEL, or the more common use is to refer to the cell you wish to convert to upper case like so:
Convert the text into lowercase
To convert the text field into lowercase letters then you need to use the LOWER function. The LOWER function takes the same format as the UPPER function:
Again this is where text can refer to either a typed word, like =LOWER(“DEDICATEDEXCEL”) which would return the result dedicatedexcel, or refer to the cell which needs to be converted:
Convert the text into Proper Case
Finally we can convert text into proper case. Proper case format means that a text field is converted so that each word starts with a capital letter and is then followed by lowercase lettering. This is extremely useful for name fields which have a first name, middle names and surname. To convert into proper case you need to use the PROPER function in the same way as the previous functions:
And like before text can refer to either typed text or the cell which you wish to convert:
The 3 functions, UPPER, LOWER and PROPER, will help convert any text field into the casing you desire.
This is particularly useful on large datasets where text fields have been manually keyed-in and are very susceptible to errors or mixed formats.
The PROPER function will help with name based fields, ensuring that each word is “properly” formatted with a capital at the start, followed by lowercase letters.
I hope you have found this a useful exercise. With these new skills you will no longer have to suffer with poor looking Excel reports because of data-entry errors and poorly formatted text fields.