If a text value has been entered into a cell in Excel with a mix of upper and lower case values that you are not happy with, you can dynamically change the case using the UPPER, LOWER and PROPER functions.
Download File
If you would like to follow along download the attachment below.
What are the UPPER, LOWER and PROPER functions
The UPPER, LOWER and PROPER functions are text functions that manipulate text output in a cell.
The syntax for each function is:
=UPPER(text)
=LOWER(text)
=PROPER(text)
The text option is the cell reference of the cell that contains the text that you want to change the case.
Using the UPPER function
The UPPER function converts all letters in a text string to upper case.
Using the LOWER function
The LOWER function converts all letters in a text string to lower case.
Using the PROPER function
The proper functions capitalises the first letter in each word, and converts all other letters to lowercase.
Using the functions to correct multiple text values
The UPPER, LOWER and PROPER functions can be applied to a range of cells with text values, and then the corrected value generated by the functions can be pasted as a value to quickly update text values to the desired case.
One useful example would be if you had text in a column and you wanted to format all the values as proper, where the first letter of each word was upper case and the remaining letters lower case.
The PROPER function can be entered once against the first value next to a column of text data that you want cleaned.
The PROPER function will now apply to each value in the dataset.
The corrected text values in the column where the PROPER function was used can then be highlighted, by left clicking on the first value and selecting all the values you want copied.
Once highlighted you can copy the values by pressing Ctrl + C, this will put a green dotted line around the cells that you have copied.
To select the area that you want to paste the corrected formats click on the first value that you want corrected, in this case the value in A2 and select all the way to the last value in the dataset, A7.
To paste the values generated from the PROPER function as a value rather than a formula press Ctrl + Shift + V.
You are then left with corrected hardcoded values in column A which have replaced the original incorrect format.
Impact on numeric values
None of the functions have any impact on numbers in text. They will still convert the letter values but numbers will not be impacted.
Conclusion
The UPPER, LOWER and PROPER functions can be used to change the case output for text values in Excel.
If you have a list of text values that you want to change, you can copy the selected function across this range, and then copy and paste as a value into the original data to correct a large amount of text in one go.
Any number values in a text string are not affected by any of the functions.
Comments