Data cleanup in Excel is common and often one of the most tedious and time-consuming tasks. I’ve had to do it when preparing reports, analyzing imported datasets, or consolidating information. If there’s one thing every experienced Excel user knows, it’s that you shouldn’t expect data to arrive in a neat, structured format. That’s why it never hurts to know a few functions that can help you structure it properly.
With the functions I’ll discuss here, you can make data cleanup much easier in Excel. They allow you to extract, style, split, and join text to get what you need without wasting hours doing it manually. So whether you’re dealing with inconsistent entries, unnecessary characters, or text that needs to be split and reorganized, Excel has got you covered.
TEXTAFTER
The TEXTAFTER function extracts everything that comes after a delimiter — a specific character, word, or symbol. The function is handy when working with data that has consistent separators, such as commas, colons, or dashes. TEXTAFTER saves you the hassle of manually copying the text after the separator and pasting it elsewhere.
5 Functions to Instantly Clean Up Your Messy Excel Sheet
These functions are like a power wash for Excel.
For example, imagine you have a list of email addresses, but you only want the list of domains (e.g., gmail.com and yahoo.com). I’ve done this before when I needed to verify domains. Since everything after the @ symbol is the domain, extracting it using TEXTAFTER is straightforward. Here’s an example formula.
=TEXTAFTER(C2, "@")
Drag the formula down to the remaining cells to extract domains from the rest of the emails.
All functions here have optional arguments, but the majority of the examples I will use are basic and don’t need them to be specified.
Another common usage of TEXTAFTER is when extracting product IDs from product codes. Many product codes are structured like SKU-12345. A formula like the one below will easily extract the 12345 part of the product code.
=TEXTAFTER(A2, "-")
After extracting the product IDs, you can use them for whatever analysis you need. You can even use TEXTAFTER to standardize them or clean up lists.
TEXTBEFORE
The counterpart to TEXTAFTER
TEXTBEFORE extracts text that comes before the delimiter — contrary to what TEXTAFTER does. It’s the perfect function for extracting prefixes, names, and categories that have been added to text. It’s invaluable when cleaning up datasets where identifiers are consistently structured.
TEXTBEFORE is the surgical equivalent of using the LEFT function. You don’t need to count characters manually—something that can fail if the text changes in length.
A good example is if you have a list of names structured as Last Name, First Name. You can easily extract the last name for better sorting and filtering with the formula below.
=TEXTBEFORE(A1, ",")
The length of the last name doesn’t matter. The comma serves as a logical stopping point instead of a hardcoded number, so the extracted text won’t be cut off if the length varies.
TEXTBEFORE is also great for removing unnecessary details from strings like Customer: John Doe or Employee: Jane Doe. If you just want to know whether they’re a customer or employee for better categorization, you can use a formula like the one below to isolate Customer or Employee.
=TEXTBEFORE(A2, ":")
TEXTSPLIT
Break complex text apart
If you have a dataset with complex strings that need to be broken down into multiple cells, the TEXTSPLIT function can help. While TEXTAFTER and TEXTBEFORE extract text only up to the first delimiter they encounter, TEXTSPLIT looks at all delimiters and splits the text into rows and columns. It’s similar to using the Text to Columns feature, but without all the clicks.
For example, suppose you have a cell containing “Apples, Oranges, Bananas” and need to convert it to a list. Instead of splitting it into individual cells manually, you can use the formula below:
=TEXTSPLIT(A1,, ",")
Keep in mind that I’ve left the second argument blank but entered the delimiter in the third argument. That’s because the second argument splits the text horizontally (into columns) while the third argument splits it vertically (into rows).
TEXTSPLIT is also useful for splitting addresses such as “742 Evergreen Terrace, Springfield, IL” into street, city, and state. This function has served me well many times, especially when analyzing geographical data or preparing mailing lists. I have even used it in scenarios where survey responses are in a single cell and need to be broken down. It’s easier to analyze responses accurately when everything is spread out rather than crammed into a single cell.
TEXTJOIN
Bring text together seamlessly
The TEXTJOIN function does the opposite of TEXTSPLIT — it combines multiple text strings into one with a delimiter you specify. I typically use it to consolidate data from different columns or to create custom identifiers.
For instance, if cell A2 is John and cell B2 is Doe, the following formula will join them together.
=TEXTJOIN(" ", TRUE, A2, B2)
The first argument is not blank but a space — that’s the delimiter. The TRUE argument ensures that blank cells are ignored, so the output looks clean and professional.
Now, let’s look at an example of creating custom identifiers. If cell A2 is Electronics and cell B2 is 12345, I can join the category and ID to form Electronics-12345 with the following formula:
=TEXTJOIN("-", TRUE, A2, B2)
I have used TEXTJOIN in scenarios where I need to do the opposite of TEXTSPLIT (e.g., combining parts of an address into one line).
UPPER, LOWER, and PROPER
Ensure consistent data formatting
The UPPER, LOWER, and PROPER functions can ensure consistency throughout your data when you’re dealing with inconsistent formatting. I usually see this level of inconsistency when entries come from different sources, such as survey responses or feedback forms. Inconsistent formatting makes the data look untidy and difficult to work with, especially when using case-sensitive formulas.
My 9 Favorite Excel Formatting Tricks to Make My Data Pop
Because even spreadsheets deserve a little sparkle.
Let’s say you have a list of customers whose names aren’t entered consistently. Some use proper case, others are all caps, and others are lowercase. If you want them all to use proper case, turning “john doe” into “John Doe,” you can use the formula below:
=PROPER(A1)
Another example is standardizing product categories. If you have entries like electronics, ELECTRONICS, and Electronics, ensuring they all appear as ELECTRONICS is as easy as entering the formula below.
=UPPER(A1)
Doing things like this ensures consistency, especially when grouping or filtering data for analysis.
Data cleanup in Excel should not be painful
Cleaning up messy data doesn’t have to be a headache. With these functions, you can extract, format, split, and combine text with ease. These tools streamline workflows, reduce errors, and save valuable time, making Excel a great ally for anyone handling large datasets. Mastering them makes your data cleanup feel almost unfair.
