Do you ever feel like you’re spending too much time fixing messy text entries in Excel? Wrestling with inconsistent capitalization, extra spaces, and endless manual edits can be a real time suck.
Good news: there’s a better way!🎉
In this article (with video!), I’ll walk you through 5 Excel text functions—PROPER, UPPER, LOWER, CONCAT, and TRIM—that will save you hours. These functions aren’t just simple to use; they’re like having a magic wand for tidying up data. Whether cleaning up customer lists or combining product descriptions, these tricks will have you working smarter, not harder.
Here’s what you’ll learn:
- Standardize text capitalization
- Combine data from multiple cells
- Remove extra spaces for cleaner results
Ready? Let’s dive in! Or check out my video below:
3 Excel Text Functions to Change Case
Have you ever stared at a list of names in Excel that looks like it came straight out of chaos? Some names are ALL CAPS, others are lowercase, and some are a mix of both. Excel’s PROPER, UPPER, and LOWER functions can fix that headache in seconds.
- PROPER – Capitalizes the first letter of each word (great for names).
- UPPER – Converts all letters to uppercase (perfect for headings or codes).
- LOWER – Converts all letters to lowercase (useful for email addresses).
If you’ve ever had that headache of inconsistent name entries—like some are all caps and others are all lowercase—these functions will be your new best friends.
Here’s how they transform messy data:
Notice the different results with these text functions. Each function refers to the name found in Column A.
💡 Pro Tip: Once you’ve cleaned up your data, make sure to replace the formulas with plain text. Use the Paste Values option to keep your data neat and formula-free (more on that below).
How to Paste Values
After using text functions, you’ll often want to lock in your results, so you’re not stuck with formulas in your cells. Or, paste the values into another column in the worksheet. Here’s how:
- Select the cells with the results you want to keep (the clean data). Copy the selection using your favorite Copy action. I like the keyboard shortcut, [Ctrl]+C.
- Next, move to the first cell, where you want to paste the results, either in a new column or on top of the formula. Right-click and pick Paste Values from the Paste options, and voilà—your formulas are gone, leaving only clean, editable text.
- .Press [Esc] to clear the paste operation.
This step ensures your data stays consistent, especially when sharing or exporting your spreadsheet.
Combining Text Entries
Do you have names split into two columns (like First Name and Last Name) and need them combined into one? Excel’s CONCAT function makes this a breeze!
CONCAT is a function that “combines” text content in multiple cells. Additional text, such as a comma or space or other text, is included in quotations to create a character string. The quote characters won’t display in your results, only the text characters inside of the quotes. In the example below, Column B is the Last Name, we want a comma and a space after the last name, and Column A is the First Name, so the CONCAT formula might look like this:
=CONCAT(B12,”, “,A12)
Here’s a bonus option using the ampersand or & sign that doesn’t need the CONCAT function. You’ll see this in Column D in the example above.
=A12&” “&B12
One way to think of it is that the ampersand glues together multiple elements, in this case, the first name in cell A12 plus a space plus the last name in cell B12.
Both approaches work like a charm. Choose the one that feels most natural and let Excel handle the rest.
💡 Bonus Tip: Remember you can add spaces, commas, or any custom text by wrapping it in quotation marks, like ” ” or “, “. The possibilities are endless!
Cleaning Up Your Data
Finally, there is a text function that cleans up data.
Have you ever wondered why your data won’t sort properly or doesn’t match your searches? Extra spaces—especially those sneaky leading and trailing ones—are often to blame. This can be a common problem when working with data from other sources, such as downloads from a different system.
This is where the TRIM function comes to the rescue. In the example above, the entries for Tommy Tomato🍅 and Peter Plum contain leading and trailing spaces.
To clean up extra spaces, use the TRIM function, which eliminates spaces at the start and end and replaces multiple internal spaces with a single space. That way, your data is neat, consistent, and ready for action.
✨ Pro Tip: Combine TRIM with other functions for even better results.
Combining Text Functions for Maximum Results
Sometimes, a single function isn’t enough to tackle messy data. That’s where nesting functions come in handy.
Let’s say, your data has inconsistent capitalization and leading spaces, you could nest TRIM and PROPER inside CONCAT, like the examples above:
=TRIM(PROPER(CONCAT(A12,” “,B12)))
This formula:
- Removes unnecessary spaces.
- Capitalizes the first letter of each word.
- Combines the text from two columns.
The result? Beautifully clean and professional-looking data in one go. And, once you’re happy with the results, Paste Values to replace the formulas with the text results you want to work with.
Which Function Will You Try First?
Whether you’re standardizing messy names, combining text fields, or cleaning up imported data, these 5 Excel functions will save you time and make your spreadsheets shine.
Want to learn more? Check out my other Microsoft Excel how-to articles or join my newsletter for exclusive tips, tricks, and freebies. Explore other helpful Microsoft Excel how-to articles.
© Dawn Bjork, MCT, MOSE, CSP®, The Software Pro®
Microsoft Certified Trainer, Productivity Speaker, Certified Speaking Professional
