The strangest way that one of my programs has been broken:

This one had me beating my head against the wall for about ten minutes:

I created a program that, among other things, using the INSTR function to look for double quotes (CHR34) in cell data in order to establish that a cell contains a measurement with inches. Once it does that it can format the data in that cell according to our company standards.

This had been working perfectly for a while now, so I was really surprised when the program failed to recognize 7 7/8” as a valid inch measurement.

After a curse-filled ten minutes or so of stepping through the program, using the immediate and watches windows to run the data every which way in order to figure out why it suddenly had a problem recognizing the double quote as character 34, I decided to look at the cell entry a bit more closely.

It turns out that the person who entered the data had entered it thusly (hyphens added by me to separate characters): 7 - (space) - 7 - / - 8 - ‘ - ‘ !! After looking at the rest of the worksheet, I found another inch mark, this one being: 2' 6''. And once again, the data was entered as 2 - ‘ - (space) - 6 - ‘ - ‘.

I have never seen someone type a double quote as two single quote marks. If it was a case of a broken key on his keyboard, I guess I could understand the workaround, but it's the same key. I am super curious and would like to ask this person why he does it this way, but I’m afraid I couldn’t do it without laughing and insulting the poor guy.

Needless to say, there is no way I can programmatically account for such weirdness -- I mean, I could, but, really, should I??

Hello MrIfOnly

It's one those errors that you can't detect with the naked eye- You have to dive deep into the data to discover it.
Kudos to you that you found the error so quickly.

I vaguely remember that this issue was brought up once in one of the forums I visit frequently.

I absolutely would ask why two single quotations are being used.

You are writing code, correct? Why not simply use the range's Replace function to replace all instances of two adjacent single-quotes with one double-quote at the beginning of your code?

Rick Rothstein said:

You are writing code, correct? Why not simply use the range's Replace function to replace all instances of two adjacent single-quotes with one double-quote at the beginning of your code?

Click to expand...

Sorry I didn't see this response earlier Rick. I can do that, but I honestly don't like having to. The thought of having to add code to account for one person's weirdness rubs me the wrong way. I did end up asking the guy why he typed double quotes that way and he said that he had no idea why he did.