Excel Geeking: Finding Those Pesky Hidden Characters

A couple of years ago, a colleague at work was having a devil of a time with an Excel file. It did something with data and dates, I don’t really remember what. What I do remember is that the data was an extract from a separate system, and then that export was dropped into an Excel file, where formulas did their magic.

Except, they didn’t do the magic they were supposed to, and that was the problem.

I knew there had to be some hidden characters in there somewhere, but I wasn’t sure how I would uncover them when I couldn’t see them. They weren’t the usual suspects: extra spaces and hard returns. Which is when I decided to create a file that would do the looking for me. It’s been such a useful tool, I’ve decided to make it available to you, my friendly reader.

So, without further ado, may I represent my Character Checker!

The premise is simple. I’m basically taking the visible characters and comparing them to an ASCII table. If certain parameters are met, then we have a problem, and WARNINGS pop up.

Easy-peasy, right? It’s not a hard thing to put together, but I figured, since I already had it built, I might as well share it so you don’t have to build your own from scratch.

You can download a copy here: https://app.box.com/s/7fos33oaqiqg88pzlsp42cw242sgpueq. To use it, paste your data as values into the yellow cell in A1, and let the file do the hard work. I hope you find it helpful.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: