Excel gives you three simple text‑case functions, but most people only use them when they’re annoyed. This article shows how to use them strategically to clean messy name data — especially when names come from email addresses.
UPPER()
This one is useful for capitalization of entire strings – serials, Employee IDs, US state abbreviations, UPS tracking numbers (that notorious Z), etc. This function takes a string and anything what is a letter is indiscriminately capitalized.
=UPPER("jiri") >> JIRI
LOWER()
Where I have used LOWER() most often was in cleaning up email addresses or creating emails from names to reverse-engineer them, based on my old employer’s email template. There is one really cool use case: data comparisons – databases are notorious for this. They need text to be precise, in order to find a match and capitalization matters. So, if you are trying to compare some data before loading into a database, you may want to turn everything to lowercase, before comparisons, to level the playing field.
=LOWER("JIRI") >> jiri
PROPER()
And this one is one of the most useful functions for capitalization. What it does is simple: it takes each word, capitalizes its first letter, and makes all other letters lower case.
=PROPER("jiri krecek") >> Jiri Krecek
I think you can already feel where this is heading. Emails! Yes! We can use this to extract full names from email addresses (provided your employer uses your full name in email and in a standardized email address template.
A perfect use case for PROPER()
john.smith@company.com
In this email example we can extract his name from the email address. Suppose it is stored in cell A1.
What this formula does is find a position of the @ (in our case it is 11) and returns the 11 left-most characters of that string. But because the @ is the 11th, we must subtract 1, to remove it from the result.
=LEFT(A1, FIND("@", A1)-1)
Then replace the dot between first and last name with a space. We built upon the above formula and added a simple SUBSTITUTE of a period for a space.
=SUBSTITUTE(LEFT(A1, FIND("@", A1)-1), ".", " ")
And, finally, fix the capitalization of his name, where we wrapped the prior formula in a PROPER() function. This makes his name lowercase and capitalizes the first letters.
=PROPER(SUBSTITUTE(LEFT(A1, FIND("@", A1)-1), ".", " "))
The result?
John Smith
This is the kind of thing that makes your colleagues go “, Damn, I had no idea Excel could do that!”
It’s not all roses and butterflies though
As great as this is, there are some limitations and I have to be honest – Excel is not very good with ethnic/cultural names. And I’m not talking about pure non-English names either, because Irish names for example are hard to get right without additional formulas to account for the “Mc” or “Mac” prefix. And don’t get me started about Irish O’ prefixes or Dutch names with “van der” in between. Excel completely butchers these, so you will need extra formulas to account for those.
• mcdonald >> Mcdonald instead of McDonald
• o’brien >> O’brien instead of O’Brien
• van der linden >> Van Der Linden instead of van der Linden
• you get the idea…
It’s not perfect, but it can do amazing things and save time, if you know its quirks and address them.
Need help?
Need help streamlining your processes or solving tricky business problems? I offer one-on-one consultations to get you unstuck fast. Book a free consultation with me today at goarcherdynamics.com.
Want more practical tips and workflow hacks? I publish them regularly on my blog — check it out and subscribe for updates: goarcherdynamics.com
Leave a Reply