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

MrIfOnly

Active Member
Joined
Nov 10, 2016
Messages
493
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??
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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?
 
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?
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.
 

Forum statistics

Threads
1,216,499
Messages
6,131,012
Members
449,613
Latest member
MedDash99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top