Ian Jamieson

In this blog post, we’ll take a look at the PROPER function in Excel, which is guaranteed to save you time and effort when handling text data.

The PROPER function will capitalise the first letter in a text string, and any other letters in text that follow any character other than a letter.

This sounds a bit confusing, doesn’t it? So let’s look at a real-life example.

I have been provided with the list of names below, which looks to have been compiled from various sources based on the mixed formatting.

I can simply use the PROPER function to standardise the data. The formula is =PROPER(cell ref) – see below:

That looks much better and has saved me from having to edit each field value individually. Phew.

As you can see, PROPER is an effective way to tidy up the names of people, places or anything that needs capital letters at the start of each word.

Note: PROPER also works with other characters like underscores or full stops, not just spaces – see image below. I’ve used PROPER to format API names that require underscores rather than spaces in their names.

Any numbers or punctuation used in your text won’t be affected by PROPER.

One thing to keep an eye out for is if any text has punctuation within the words. For example:

“john O’dowd” will become “John O’Dowd’ after PROPER treatment, and you may not want the first D in O’Dowd to be capitalised. Just keep an eye out.