Contextures

Home > Formulas > Text > Text Case

Excel Case Functions - Proper, Lower, Upper

Use the Excel case functions to change the case of text in Excel -- PROPER, LOWER and UPPER. These functions change the case of letters in a text string, and have no effect on other characters.

change letters to uppercase

Video: PROPER Function

When people enter their contact information in online forms, the data you collect might be messy. This video has text in upper, lower, proper and mixed case. Use the PROPER function to clean up the first and last names, and make them consistent.

Video Timeline

  • 00:00 Introduction
  • 00:15 Messy Data
  • 00:34 Make a Table
  • 00:59 Add Columns
  • 01:22 Add 1st Formula
  • 01:38 Add 2nd Formula
  • 01:54 Contextures Link

Video Transcript

If you'd like to read the PROPER function video transcript, click on the green check box below. When you're finished, you can click the check box again, to hide the transcript

Here is the full transcript for the PROPER function video.

Introduction

Let's take a look at an easy way to fix a common issue. Here you can see that some of the entries are all lower case, some were caps. Let's fix it really easily.

Messy Data

This example shows new subscriptions for your newsletter, but it could be data from any kind of form entry -- attendees, recent orders, you name it. Any time that humans are entering their names, the chances are that the report you get will not be uniform.

This can be a pain, if you need to use that data. Let's fix it really easily

Make a Table

I'm going to make this a table, because it will make it much easier to copy down the formula. To format it as a table:

  • First I'll select a cell inside the data
  • Then i'm going to go up to the Home tab
  • Select Format as a Table
  • I'm selecting this black and white design
  • And because i have headers, I'll make sure that's checked off
  • Click OK, and here you go, it's formatted as a table

Add Columns

We're going to insert a couple of columns for our new properly formatted first and last names

  • I'll right click and then I will select Insert Column to the left
  • And i'm going to do that twice, so that I have a column for first name and last name
  • I'll just fix the heading here first: Name proper
  • And Last Name Proper

Add First Formula

We're going to use a formula for proper

  • Start with =PROPER(
  • Then we'll select the first name in the list
  • Close the bracket and press Enter
  • Now, because it's a table, the formula is copied down

Add Second Formula

Next let's repeat it for the last names

  • Again, =PROPER(
  • Select that last name
  • Close the bracket, and press Enter

And there you go!

PROPER Function

When people enter their contact information in online forms, the data you collect might be messy.

This contact list has text in upper, lower, proper and mixed case.

messy data with mixed case

NOTE: The PROPER function only works correctly on names that have a single capital letter, at the start of each word in the name. For example, "de Bruin" is the correct spelling, but PROPER would change that to "De Bruin".

Excel LOWER Function

The LOWER function changes any uppercase letters in a text string to lowercase.

Note: The LOWER function does not have any effect on other types of characters -- it only changes letters.

LOWER Function Syntax

There is only one argument in the LOWER function syntax, and it is required:

=LOWER(text)

  1. text: The text string that you want to change to all lowercase

LOWER Function Example

In this example, the LOWER function is used in a named Excel table, and a structured table reference is used in the formula:

=LOWER([@email])

In the EmailFix column, all letters in the email addresses are changed to lowercase.

change letters to lowercase

Excel UPPER Function

The UPPER function changes any uppercase letters in a text string to uppercase.

Note: The UPPER function does not have any effect on other types of characters -- it only changes letters.

UPPER Function Syntax

There is only one argument in the UPPER function syntax, and it is required:

=UPPER(text)

  1. text: The text string that you want to change to all uppercase

UPPER Function Example

In this example, the UPPER function is used in a named Excel table, but a normal cell reference is used in the formula:

=UPPER(B4)

In the CodeFix column, all letters in the product codes are changed to uppercase.

change letters to uppercase

Name Fix Challenge

If people enter their names in a sign-up form, you might end up with a mixture of upper and lower case letters. You can see an example in the screen shot below

Your challenge is to clean up that list. Using Excel formulas, make these changes to the list:

  1. Show the corrected name in column B, with only the first letter of each name in upper case. For example, Fred Jones instead of FRED JONES.
  2. Add an X in column C, to mark the names that were fixed.
  3. In cell E1, show the number of names that were fixed.

You can type your own list, or download my sample file. For inspiration, there are videos and links on my Functions page.

There are solutions in the next section

fix the names challenge

Name Fix Solutions

In the previous section, your challenge was to clean up a simple list of names, using Excel formulas. I posted this challenge in my Excel newsletter too, and readers sent in their solutions.

  1. Show the names in the correct upper and lower case. Most people used PROPER: =PROPER([@Name])  OR  =PROPER(A2)
  2. Put an X in column C, if the name had been corrected. Most people used IF and EXACT: =IF(EXACT([@Name],[@Fixed]),"","X")
  3. Show a count of all the changed names. Almost everyone used COUNTIF: =COUNTIF(Table1[Changed?],"x")

To see the different solutions, including some very interesting approaches, download the completed Name Case Solution workbook. The zipped file is in xlsx format, and does not contain any macros.

Get the Sample File

PROPER Video: Download the PROPER function sample file to follow along with the video. See how to use the Excel PROPER function to clean up the first and last names, and make them consistent. The zipped file is in xlsx format, and there are no macros in the file.

Case Functions: Download the Case functions sample file with PROPER, LOWER and UPPER examples. The zipped file is in xlsx format, and there are no macros in the file.

More Tutorials

Sum Functions

Count Functions

Count Criteria in Other Column

Functions List

 

About Debra

 

Last updated: November 8, 2022 8:03 PM