Stop Cleaning Excel Data Manually. Use These 3 Formulas Instead

Let’s be honest: nobody wakes up excited to clean a spreadsheet.

You open a new data export, and it’s an absolute horror show. Names are randomly capitalized, invisible trailing spaces are quietly breaking your formulas, and employee IDs look like encrypted government codes.

You could spend the next three hours manually typing, copy-pasting, and cursing at your monitor. Or, you could let Excel do 100% of the heavy lifting while you go grab a coffee.

If you want to stop wrestling with your data and start analyzing it, you need to add these three game-changing, under-the-radar Excel formulas to your toolkit immediately.

1. The Invisible Enemy Destroyer: TRIM meets CLEAN

Have you ever written a flawless XLOOKUP or VLOOKUP, verified that the data matches perfectly with your own eyes, but Excel stubbornly spits back a #N/A error anyway?

Welcome to the nightmare of invisible characters. Database exports love to sneak hidden spaces or non-printable system codes into your cells. To the human eye, it says "Apple". To Excel, it says "Apple \x02".

The fix is a beautiful, one-two punch formula:

Excel

=TRIM(CLEAN(A2))

The Magic Breakdown:

• CLEAN instantly vaporizes non-printable characters and weird line breaks that sneak in from web scrapes or legacy database exports.
• TRIM aggressively strips out all leading and trailing spaces, while reducing any accidental double-spaces between words down to a single space.

The Data Pro Rule: Before you do any lookup, matching, or math, run your raw text column through =TRIM(CLEAN()). It will save your sanity and eliminate 90% of your troubleshooting time before it even starts.

2. The Clunky “Text-to-Columns” Killer: TEXTBEFORE & TEXTAFTER

For decades, if you wanted to extract a specific piece of text from a cell — like grabbing just the domain from an email address (john.doe@company.com)—you had to write a horrific Frankenstein formula using LEFT, RIGHT, MID, and FIND.

If you are using modern Excel (Excel 365 or 2021+), you can officially throw those old tutorials in the trash. Say hello to TEXTBEFORE and TEXTAFTER.

Get Simmi Parmar’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Instead of counting characters, you just tell Excel what character acts as the divider (the “delimiter”) and which side you want.

How it works in the real world:

Imagine a messy list of product codes: PROD-90210-US.

• Want the product type? =TEXTBEFORE(A2, "-") → Returns PROD
• Want the country code? =TEXTAFTER(A2, "-", -1) (The -1 tells it to look from the end) → Returns US
• Want the ID in the middle? Just nest them: =TEXTBEFORE(TEXTAFTER(A2, "-"), "-") →Returns 90210

It’s intuitive, it’s clean, and it adapts dynamically if your text changes length.

3. The Nested IF Killer: SWITCH

Data cleaning almost always requires categorizing messy inputs. For example, you need to map internal code abbreviations to human-readable names (e.g., “N” becomes “North”, “S” becomes “South”).

Historically, this meant writing a nested IF statement that looked like a coding nightmare: =IF(A2="N", "North", IF(A2="S", "South", IF(...))). Miss one parenthesis at the end, and the whole thing collapses.

Enter the SWITCH function—the cleanest way to map data in Excel history.

SWITCH looks at a single cell, matches it against a list of triggers, and swaps it out on the fly.

The Syntax:

Excel

=SWITCH(expression, value1, result1, value2, result2, ..., default_result)

See it in action:

Excel

=SWITCH(A2, "N", "North", "S", "South", "E", "East", "W", "West", "Unknown Region")

It reads like plain English. If you ever need to add a fifth or sixth region, you just drop it right into the sequence without creating a massive trail of closing parentheses.

Stop Working Harder. Start Working Smarter.

The difference between a stressed-out data analyst and a highly efficient one isn’t the size of their brain — it’s the efficiency of their toolkit. By letting TRIM/CLEAN handle your formatting bugs, TEXTBEFORE/TEXTAFTER slice up your strings, and SWITCH handle your logic, you'll slash your data prep time to fractions of what it used to be.

Go ahead — open up that messy spreadsheet waiting for you and give these a shot. Your afternoon just freed up.

What is your absolute biggest spreadsheet pet peeve when inheriting someone else’s data?

Drop a comment below and let’s troubleshoot it together!