Authored by Vince Russell on 12 July 2005 at 10:38PM
Current rating: 3 out of 5 stars!
Filed under: Excel Tips

Formatting names correctly

Excel is exactly what you need!

The PROPER function works great for company names and the name of persons (real or fake) but there may be a problem, if someone has entered "ABC Limited" as the company name, PROPER will format this as "Abc Limited" (horrible eh), so we need a way to format only those entries that are all in upper case or all in lower case. Anything that is in mixed case, as in those that have already been formatted, we can leave as they are.

Guess what? Excel can do this as well, awww, it's such a good little application isn't it, a round of applause for Microsoft!

First, let's write what we need to do in plain English, I think it goes something like this…

If the value we wish to format is all uppercase or all lowercase
Then, use the PROPER function on that value
If not, take the value as it is

The first part is pretty easy, all we need to do is see if the value we want to format is exactly the same as it's upper or lower case equivalent, for this, we can use the EXACT function.

Syntax: EXACT(text1, text2) will return True if text1 is exactly the same as text 2, False otherwise.

Here are some examples

=EXACT("Vince", "VINCE") will return False
=EXACT("Fred", "VINCE") will return False
=EXACT("Vince", "Vince") will return True
=EXACT("VINCE", "VINCE") will return True

Now, if the value we're trying to format is in cell A1, to see if it differs from its uppercase equivalent, we can put this formula in cell B2

=EXACT(A1, UPPER(A1))

or to check against the lowercase value

=EXACT(A1, LOWER(A1))

Again, referring back to our English description of what we want to do…

If the value we wish to format is all uppercase or all lowercase
Then, use the PROPER function on that value
If not, take the value as it is

We can use then use the IF function to come up with this masterpiece (the returns are for readability only)…

=IF(EXACT(A1, UPPER(A1)),
PROPER(A1),
A1)

This formula will only format the text if the value is all uppercase, if we want to expand this to handle all those in all lowercase as well, the formula becomes…

=IF(OR(EXACT(A1, UPPER(A1)), EXACT(A1, LOWER(A1))),
PROPER(A1),
A1)

All that's left is to copy the formula to the other cells to make everything else look great. ABC Limited won't be complaining to you that their company name is poorly formatted and I'll be happy because you've formatted my name the way I like it!

With the dot above the i as a smily face if that's ok!

Download File Download the example Excel file

Authored by Vince Russell on 12 July 2005 at 10:38PM
Current rating: 3 out of 5 stars!
Filed under: Excel Tips


Comment on this article

  

  


Remember Me?