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.
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:
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".
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 "email@example.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
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.
My name is Radu Meghes, and I'm the owner of excelexplained.com. Over the past 15+ years, I have been using Microsoft Excel in my day-to-day job. I’ve worked as an investment and business analyst, and Excel has always been my most powerful weapon. Its flexibility and complexity make it a highly demanded skill for finance employees. I launched excelexplained.com back in 2017, and it has become a trusted source for Excel tutorials for hundreds of thousands of people each year.
If you'd like to get in touch, you can contact me on LinkedIn.
Thousands of people have benefited from my free Excel lessons. My only question is, will you be next?
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.