Excel PROPER Function (Useful Examples + Video)

In Excel. you can quickly change the case of the text in a cell (to lower case, upper case, or proper case) using text functions.

Below is an example of each type of case:

Lower Case, Upper Case, Proper Case - Example Text

Excel PROPER Function – Overview

PROPER function is one of the many text functions in Excel.

What Does it Do?

It takes a string as the input and returns a string where the first letter of all the words has been capitalized and all the remaining characters are in lower case.

When to Use it?

Use it when you have a text string and you want to capitalize the first alphabet of each word in the text string and make all the other character in lowercase. This could be the case when you have names in different formats and you want to make it consistent by capitalizing the first alphabet of the first and the last name.

Proper Function Syntax

=PROPER(text)
  • text – the text string in which you want in capitalize the first letter of each word.

Examples of using PROPER Function

Here are some practical examples to show you how to the PROPER function in an Excel worksheet.

Example 1 – Making Names Consistent

Suppose you have the dataset as shown below:

Excel Proper Function - Example 1

The names in this dataset are all inconsistent.

You can use the PROPER function to make these consistent (where the first alphabet of each name is capitalized and rest all are small).

The below formula would do this:

=PROPER(A2&" "&B2)

Result Proper function in Excel - Example 1

In the above formula, I use the ampersand operator to add the text in cells in column A and B, and then PROPER function makes the combined string consistent.

Example 2 – Making Address Consistent

Just like the names, you can also use it to make the address consistent.

Below is an example dataset where the addresses are in an inconsistent format:

Dataset for Proper formula Excel - example 2

You can use the below formula to make all these addresses in a consistent format:

=PROPER(A2)

Note that this formula works perfectly, but if you want the state code (such CA, NV, NY) in upper case, it will not be done with PROPER function only.

In that case, you need to use the below formula:

=PROPER(LEFT(A2,FIND("@",SUBSTITUTE(A2,",","@",LEN(A2)-LEN(SUBSTITUTE(A2,",",""))),1)))&RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,",","@",LEN(A2)-LEN(SUBSTITUTE(A2,",",""))),1))

You can get an idea of how this formula works from this tutorial.

The PROPER function works by analyzing non-text characters in a string. When it finds a non-text character, it capitalizes the next following character. While this works great in most of the cases, in some scenarios, this may not work as expected. For example, if you use the formula on the text – it’s awesome – it will give you the result as It’S Awesome. As it capitalizes the character after the non-text character, it does that with an apostrophe in this case.

Some useful things to know about the PROPER Function:

  1. The PROPER function only affects the first character of every word in a text string. All the other characters are left unchanged.
  2. It capitalizes the first letter of any word that follows a non-text character. For example: =PROPER(hello,excel) returns Hello,Excel
  3. Numbers, special characters, and punctuations are not changed by the PROPER function.
  4. If you use a null character (or a reference to an empty cell), it will return a null character.

Other Useful Excel Functions:

  • Excel FIND Function: Excel FIND function can be used when you want to locate a text string within another text string and find its position. It returns a number that represents the starting position of the string you are finding in another string. It is case-sensitive.
  • Excel LOWER Function: Excel LOWER function can be used when you want to convert all uppercase letter in a text string to lowercase. Numbers, special characters, and punctuations are not changed by it.
  • Excel UPPER Function: Excel UPPER function can be used when you want to convert all lowercase letter in a text string to uppercase. Numbers, special characters, and punctuations are not changed by it.
  • Excel REPLACE Function: Excel REPLACE function can be used when you want to replace a part of the text string with another string. It returns a text string where a part of the text has been replaced by the specified string.
  • Excel SEARCH Function: Excel SEARCH function can be used when you want to locate a text string within another text string and find its position. It returns a number that represents the starting position of the string you are finding in another string. It is NOT case-sensitive.
  • Excel SUBSTITUTE Function: Excel SUBSTITUTE function can be used when you want to substitute text with new specified text in a string. It returns a text string where an old text has been substituted by the new one.
Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

2 thoughts on “Excel PROPER Function (Useful Examples + Video)”

  1. Thanks!
    How do I make Proper() to fix the text it returned and not the formula? e.g. If I say Proper (A2) and excel returns the text in A2. After that if I delete cell A2, the returned text also disappears. How do I make sure it stays?

    Reply

Leave a Comment

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free Excel Tips EBook Sumit Bansal

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster