Archer Dynamics

After having worked in Excel for over 20 years I’m jaded, because a mere glimpse of #N/A makes my adrenals work overtime. How many hours have I wasted hunting for dirty data when the VLOOKUP function was not returning what I expected. Another white space or invisible characters, I always thought. It can be real chaos, because it feels like fighting off radiation with a fly swatter. You can’t see it.

Today, we’re going to see what options we have to help arm you with three weapons for your defense to fight back.

The crime scene

 John   Smith ,john.smith@email.com, Billing ,"Payment   Failed"
Sarah     Johnson,  sarah@company.net,Technical,"Can't Login  "
Mike Chen   ,mchen@gmail.com ,   Returns,"Wrong     Item    "

Looks fine at first glance, but this data is riddled with:

• Leading spaces
• Trailing spaces
• Multiple internal spaces (just why???)
• Non-printable characters (those pesky ASCII 0-31 chars)

Cleansing basics, the best remedy for whitespaces

Your data looks somewhat alright, but even then you see something is off – there is visible misalignment and some strings have just way too many spaces between words for you to say they don’t exist.

TRIM(A2)

Apply TRIM() function. It will clean up leading and trailing spaces and reduce to a single space between words. Then just copy that formula across the row and down for all records.

This formula works really well, but be aware of one of its quirks: it turns everything into a text string. Everything. Look at the Ticket ID and the Date columns: ID turned into a text (you can tell, because it is now left justified – a dead giveaway) and the date turned into a Julian format, also a text and trying to format it in the Ribbon will do you no good.

These two columns need an extra step and that is to make them not just clean the record, but to display the record’s VALUE()

=VALUE(TRIM(A2))

This will give perfect formatting and your data is now clean and free of rogue whitespaces. Look here:

Cleansing basics, removing the invisible

While TRIM() handles visible spaces, CLEAN() hunts the invisible demons—line breaks, tabs, and those cursed non-printing characters. If you’ve ever had this weird issue where your pivot tables looked totally off when you tried to add description fields to the data quadrant, you likely encountered carriage returns / line breaks. Someone may have hit SHIFT+ENTER mid sentence. It won’t show as a character, but it starts a new line. And pivot tables do not like it. This problem is very common when data is pasted from PDF files, which are riddled with these invisible ASCII characters (0-31). Yes, you could use RegEx to clean this up in one fell swoop, but Excel can do some of it too

=CLEAN(G2)

The best for last: can’t ditch it? Replace it

When TRIM and CLEAN aren’t enough or are too heavy-handed, SUBSTITUTE lets you get really surgical. This is your weapon against specific whitespace patterns or when you need to replace one type of space with another, or a specific character. with SUBSTITUTE() you explicitly define what you are replacing and with what.

=SUBSTITUTE(C2," "," ")
You specify:
which cell to apply it to
what character or string to look for
what character or string to replace it with
Here I am looking for a space and replacing it with a space,
to prove a point, so this formula will do nothing

There is a nifty way to nest multiple SUBSTITUTE formulas inside each other to clean up multiple scenarios all at once

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F13,"     "," "),"    "," "),"   "," "),"  "," ")
Or for easier reading:
=SUBSTITUTE(
    SUBSTITUTE(
        SUBSTITUTE(
            SUBSTITUTE(F13,"     "," "),
        "    "," "),
    "   "," "),
"  "," "
)

This nested formula will look for spaces 5 characters long and replace with a single space, then look for 4-character spaces and do a single, then for 3, then for 2. Till there are none left, other than single spaces.

Hidden gem

There is one more ugly scenario I’ve seen way back and that is a non-breaking space (CHAR(160)) when things are copied from websites.
I don’t know why but TRIM won’t fix them. The only way I learned to clean these up is to explicitly state in the formula that it is a CHAR(160) character and replace it with a regular space.

=SUBSTITUTE(C2,CHAR(160)," ")

What are your favorite data cleansing tricks? Don’t tell me Alteryx, please. That is a known fact, redundant to even mention! :-)

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