Sometimes when you import text from another source it may not be formatted in the case you want. For example it may be ALL IN CAPITALS, all lower case, or a combination of the two.
There are a few functions in Excel that we can use to quickly fix the formatting of text, namely UPPER, LOWER and PROPER.
As you’d expect the:
- UPPER function changes all text to CAPITALS
- LOWER function changes all text to lower case
- PROPER function changes the first letter in each word to upper case and all other letters to lower case – this is useful for fixing names.
The syntax for each function is basic:
- =UPPER(text)
- =LOWER(text)
- =PROPER(text)
In the table below we can see how each function has altered the original text in column A.
Our formulas in row 6 for each function are as follows:
- Cell B6 =UPPER(A6)
- Cell C6 =LOWER(A6)
- Cell D6 =PROPER(A6)
Once you have converted your text to the format you prefer you can Copy and Paste Special > Values to the cell location you want.
That was very simple. Thank you.
Glad you liked it, Wanda.
I thought you could only do this in MS Word?!! This is great! Been trying to find out how this could be done. Additional excel knowledge! Thanks!
Proper was a lifesaver when I downloaded voters lists in all sorts of conditions (all uppercase, lowercase, or anything in between). When doing mailings it really helps to make them look professional.
That being said, it takes more than PROPER to get my name right (break up the names with MID, or do a FIND function to determine where the “c” is plus one).
Hi Kevin,
At least you only have to contend with name issues in Excel. I experience confusion over my name every day 🙂
Mynda.
Typo:
“lower cast” in
■PROPER function changes the first letter in each word to upper case and all other letters to lower cast – this is useful for fixing names.
above.
Great information!
Jon
Cheers, Jon. Thanks for spotting the typo. All fixed now 🙂
Awesome! Thanks
🙂 Thanks, Alex.