You are not going crazy

“One, two, three, four, five, …, eleven, twelve. Twelve characters.”

“So why does Excel len() function gives me 13?”

“Maybe there’s a blank character at the front. Let’s try =left(cell, 1)…Hmmm, R.”

“Maybe it’s at the end. Try =right(cell, 1)…An e, nope.”

Normally, if you are working with a download from SAP, you will be dealing with an Excel flat file or pivot table with numbers or data coming in cleanly. However, if you have to work with a download from a smaller scale software such as Mindbody or OneSite, you might have to deal with unusual downloads. Such downloads will insert blanks at the front or the end of the cell, thus increasing the length of the data in the cell. Today, I faced something really unusual: the blank was not at the front or end – it was in the middle.

Below is a screen snapshot of the puzzle in question. I was trying to find the word ResidentName but I couldn’t find it.

It turns out the blank was in the middle, between Resident and Name. If you look at the formula bar, you will see that the single word is split into words.

So the blank is found in the middle, except, it really wasn’t a blank. It was an altogether different character. So a new function was added to my repertoire today: CODE(). CODE() will give you a number representing that character which if you then use in the function CHAR(), the character itself will be shown. As an example, CODE(a) gives you 97 which if you input into CHAR(97), you will get a back. CODE(A) gives you 65 which if input in CHAR(65) returns A. Small letters and capital letters have different numeric codes.

So that blank which is not a blank has a CODE of 10 which I think is an “add a line” character. In order to “see” ResidentName via formula, I have to look for it as “Resident”&char(10)&”name”. To figure out the CODE, I had to isolate the invisible character by doing Mid(cell,9,1), then apply CODE() to the result.