Pinterest Pixel

Cleaning Data with Excel’s PROPER Formula

  What does it do? Capitalizes the first letter in a text string and any other letters... read more

Download Excel Workbook
Bryan
Posted on

Steps To Follow

Overview

Cleaning Data with Excel's PROPER Formula | MyExcelOnline Cleaning Data with Excel's PROPER Formula | MyExcelOnline

 

What does it do?

Capitalizes the first letter in a text string and any other letters in the text that follow a space. Converts all other letters to lowercase letters.

Formula breakdown:

=PROPER(text)

What it means:

=PROPER(this text cell)


There are times when you will need to only CAPITALIZE the first letter in each word.  The most common scenario would be when you receive data with employee names, countries or cities that are all in lower or uppercase.

The PROPER formula in Excel can help you format the text very very easily…goodbye manual adjustments!

In our example below, we have a list of names in capital letters.  However we want to show the capital letters only on the first letter of each word.

I explain how you can do this and please go to the bottom of the page to see the animated gif tutorial:

Cleaning Data with Excel's PROPER Formula | MyExcelOnline

download excel workbook Proper.xlsx

STEP 1: We need to enter the Proper function next to the cell that we want to clean the data from:

=PROPER

 

See also  FIND Formula in Excel

STEP 2: The Proper arguments:

text

Which text do we want to change?

Reference the cell that contains the text string:

=PROPER(C10)

 

Cleaning Data with Excel's PROPER Formula

 

STEP 3: Do the same for the rest of the cells by dragging the PROPER formula all the way down using the left mouse button.

Note that all of the names are now properly formatted:

Cleaning Data with Excel's PROPER Formula

 

 

 

 

If you like this Excel tip, please share it
Cleaning Data with Excel's PROPER Formula | MyExcelOnline Cleaning Data with Excel's PROPER Formula | MyExcelOnline

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...