Cleaning up text in Excel with PROPER

Sometimes you have text in Excel that just hasn’t been entered very well!

Don’t fancy re-typing it all?

You can use a function in Excel called PROPER() to make it all nice.

In other words…

From this:To this:
Joy warrenJoy Warren
april bulmanApril Bulman
LYN LAWSONLyn Lawson

1. Insert a new column and add the following formula…

The =PROPER([@NAME]) function converts the text in a cell into “proper” case – it basically capitalises the first letter of each word.

2. Press ENTER and your formula populates downwards…

The data in the new column is of course the result of a formula, not the actual data so you’ll need to copy and paste it (as data) into the first column…

3. Copy the new column and choose “Paste Special” over the first column…

4. Select “Values”…

This will takes the results of the PROPER formula in the 2nd column and paste just the results (i.e. the correct data) over the first column and the case errors that are there.

5. Delete the column with the PROPER formula…

Then you’ll end up with a newly cleaned up first column of data…

Here’s a demo video that shows you how…

Leave a Reply

Your email address will not be published. Required fields are marked *