Proper Formula in Excel

Proper Formula in Excel

As the name suggests, Proper Formula in Excel transforms the input string into the proper case.

Suppose there is a data which consists of 10,000 entries in different columns and the value in two columns Name and City are in small letters and want to capitalize the first character as we know the Name and City name come under noun and we have to start it with uppercase and this is where the Proper Function comes into play.

Excel Proper Formula converts and capitalizes the first character of the string and remaining all the other characters same i.e in lowercase. While converting the string using Proper Function, the numbers and punctuations in a string will not get affected by it.

Proper Function is classified as Text function because it results in converting the first character of the string to uppercase and all remaining characters to lowercase.

we can find the function under Formula tab -> Text function -> Proper.

From the above figure, we get to know about the location of the PROPER formula and from here we can directly load the function for use.

Formula of Proper Function

It takes one argument, which is a string whose first character will be transformed to uppercase and the rest of the characters remains in lowercase.

There are two ways to load the proper formula in excel

1. loading it manually (clicking PROPER function under the Text function)
2. writing the function in the cell.

How to Use Proper Formula in Excel?

Below are examples of Proper Formula in excel.

Example #1 – Loading Proper Formula Manually

We have a dataset in Excel which contains the Name, City, Shipping Cost, Order Priority, Customer ID. Name and City are given in the small letters and we have to capitalize the first character of all the observation which comes under Name and City. Doing it one-by-one is a very difficult task and may consume nearly about 15-20 minutes. Here, for ease, we will use the PROPER function.

In this example, we will load the function manually.

Steps: –

• Click on the cell where we have to put the required text.

Here, we have selected the F2 cell.

• Click on the Formulas tab then, under the Function Library group click on the TEXT When we click on the Text we’ll see lots of functions are there to use. But we need a PROPER formula which is shown below.

• After clicking, we will see that a dialog box appears with an input field in it named as Here, in this field we have to put the text argument which is required by the PROPER function.

•  It is a reference to a cell containing text. We will pass here the Name of the customer i.e. in A2.

In the above picture, we see the difference between the input value and the results. As we can see that Formula results are showing, which is Lane Daniels. The first character of both the word is capitalized and the rest of the character remains the same i.e in lowercase.

• After that, we will hit the OK button and the results will be displayed in the cell.

As we see that the first character of each word in F2 is capitalized.

To convert all the name in the given format, we will just drag the cell from the bottom. After this, all the name has been converted to the same format.

Example #2 – Writing the Proper excel Formula in Cell

Using the same above example, we will now change the characters of City using Proper formula.

 In this example, we will load the function manually.

Steps:

• Select the cell where you want to put the required text. In this case, we have selected.

• In the second step, write the excel formula =PROPER(text).

• Then select the argument to be passed in PROPER Here, we have to pass the string because this function returns a string value. We will pass the reference to the cell containing text. We will pass here the City i.e. in B2 and close the parenthesis.

• Press Enter to get the result.

As we see that the first character of each word in G2 is capitalized.

To convert all the name in the given format, we will just drag the cell till the bottom. After this, all the City name has been converted to the same format.

Things to Remember

1. Proper Formula in Excel converts and capitalizes the first character of the string and remaining all the other characters same i.e in lowercase.
2. While converting the string using Excel Proper Formula, the numbers and punctuations in a string will not be get affected by it.

You can Download this Proper Formula Excel template here – Proper Formula Excel Template

Recommended Articles

This has been a guide to Proper Formula in Excel. Here we discuss examples of Proper Formula to convert the first character of the string to uppercase and all remaining characters to lowercase with downloadable excel template. You may learn more about excel from the following articles –

• Create Formula in Excel
• Frequency Formula in Excel
• Rate Formula in Excel
• Forecast Formula in Excel
• TIME Formula in Excel