How to change case in Excel to UPPER, lower or Proper
If you have worked in Excel then you know there are times when you want to make text UPPER or lower case. While other Office products like Word or Outlook have a shortcut for this (it’s Shift+F3 by the way), Excel does not have a quick way to convert your text. Instead, Excel provides 3 functions to convert text to “UPPER”, “lower” or “Proper” cases.
How to use UPPER, LOWER, and PROPER functions
All three functions have only one argument: text. Basically, all you have to do is provide the text you want to convert and the function will do the rest. Since you will rarely have all your text in one cell you can use concatenation for more flexibility.
Cells C3 to C5 contain the following formulas:
C3: =UPPER(B3)
C4: =LOWER(B4)
C5: =PROPER(B5)
To better understand the way each of these functions works, I have prepared another example that is closer to a real-life task.
In the Excel workbook we have the ID, First name, and Last name of the employees of Company, Inc. However, the First name is properly capitalized (“Emma”), but the Last name has all letters capitalized (“SMITH”). If we want to get the First and Last name in the same cell we can use concatenation, combined with UPPER, LOWER, or PROPER function to adjust the text based on our needs.
For example, cell E4 contains the formula =UPPER(C4&" "&D4) to combine the First and Last name of the employee and capitalize all the letters. The symbol & is used to concatenate the text from cell C4 with a space " " and then with cell D4. The result we were looking for is “EMMA SMITH”. Using the same formula but with the LOWER function will generate “emma smith”, while using the PROPER function will result in “Emma Smith”.
Generating employee email address using LOWER function and concatenation
Taking our example a bit further, let’s assume that we want to generate two more data sets. The first is based on the structure “Last Name, First Name (ID)” while the second is an email address written as “first.last@company.com”.
Cell H4 contains the formula =PROPER(D4&", "&C4&" ("&B4&")") which converts the text from cells B4, C4, and D4 using the PROPER function and concatenates the content. The result is “Smith, Emma (1235)” and corresponds to the format shown in cell I1. Whatever value you add between quotation marks "" will be added to the final concatenated result.
To generate the email address for each employee, we can use the LOWER function, concatenate the first name and last name, and add the email domain “company.com”.
The formula I used to achieve this is =LOWER(C4&"."&D4&"@"&email_domain) where email_domain is the named range for cell I2. I prefer using named ranges because it makes the formula easier to understand.
You can download the Excel file I’ve used from the link below.
If you have additional questions please let me know by posting a comment.
