Text in quotes not case sensitive anymore?

Has anyone else noticed that text within quotes is no longer case sensitive? I had noticed that column names weren't case sensitive anymore a while back which wasn't an issue because when you name a column, case doesn't matter. You can't name a column as "Column" and then name another column as "column".

But this is the first time I have noticed this within quotes and it could cause me some issues.

In the screenshot below you will see that [Column2] is set as a checkbox column with a very basic formula in it to check if the Primary column contains the text of "Yes".

The row that contains "yes" is checked even though the first letter is not capitalized.

This could be helpful in some cases, but it could also hinder other cases.

Thoughts anyone?

I personally would prefer if it were case sensitive. It would allow me to be much more specific with criteria. If I am not too worried about capitalization I can always use an UPPER or LOWER function very easily.

I have one case in particular where I am using a conversion table and the upper case version of a letter could have a different value than the lower case version. I need to do some further testing, but if I am using an INDEX/MATCH to pull from a conversion table, will it pull the correct value based on upper vs lower case?

I will revisit this post after further testing, but this could have some major impact on things. At least for me it could...

Depending on the results, I may end up having to put in a Product Enhancement Request to change it back.

Find more posts tagged with

Formulas

Enhancement Request

Comments

Paul Newcome

Just finished up some testing, and...

It isn't even just text within quotes. It also includes text within cell references.

This does NOT work for me. Anyone else?

Basic layout of sheet...

Primary Column2 Column3 Column4

a 1 a f1

A 2 A f2

b 3 f3

B 4 f4

Tests run...

f1:

=INDEX([Column2]:[Column2], MATCH([Column3]@row, Primary:Primary, 0))

result: 1 (expected)

f2:

=INDEX([Column2]:[Column2], MATCH([Column3]@row, Primary:Primary, 0))

result: 1 (based on upper v lower case, this should have been a 2)

f3:

=INDEX([Column2]:[Column2], MATCH("a", Primary:Primary, 0))

result: 1 (expected)

=INDEX([Column2]:[Column2], MATCH("A", Primary:Primary, 0))

result: 1 (based on upper v lower case, this should have been a 2)

Mike Wilday

Wow. that is so specific. I haven't had to worry about casing in my real-life examples, but yes. That is important. I could see how not deferring to exact casing in Match would be an issue. Those zeros should indicate an exact match of letter and casing. That would be the expected behavior. Have you submitted this to support yet?

Paul Newcome

I put in a product enhancement. Didn't think a support ticket would amount to much as it is probably an intentional change.

It's not even just in MATCH functions either. It acts exactly the same way in an IF statement checking a box.

=IF([Column Name]@row = "a", 1)

provides the same result as

=IF([Column Name]@row = "A", 1)

I accidentally stumbled across this change this morning when testing something out for a different community post.

I was checking a box if the cell had "Yes" in it. I populated a few more rows with junk data including "yes", and the box was checked for that row as well.

That's what prompted the further testing and discovery which led to the larger implications of the INDEX/MATCH not pulling the correct data.

I have a rather complicated sheet that is essentially the main driving factor for an entire project that a lot of people rely on for it's accuracy as a huge time saver. This sheet NEEDS to be able to differentiate between capital vs lower case letters for conversions.

Without that differentiation, the entire project may as well be tossed in the trash which means I wasted MONTHS of time building this calculator, and everyone is going to have to go back to doing it by hand which will increase the time spent on one calculation from a few seconds to a few minutes.

When I do an easy one by hand, it only takes maybe 4 minutes. Lets say I have 15 of those. There's an hour spent doing these calculations.

Those same 15 in the calculator I built would only take about 2 minutes TOPS.

That's increasing the time spent by what... About 3,000%?!? And that's for the easy ones.

The more complicated ones can take even longer by hand, but they would take the same amount of time using my calculator. That all depends on how fast someone can type (or copy/paste).

Sorry... It's just frustrating to see so much work go to waste. Rant end...

Mike Wilday

Rant approved!

Andrée Starå

Sounds like a bug to me! Let's hope that it is!

rant

Paul Newcome

Fingers crossed that's all it is, but it is consistent across all sheets new and old.

Paul Newcome

Haha. Thanks, Mike.

Andrée Starå

I hope that it might have been the latest update that caused it!

Paul Newcome

I am not even sure what the latest update was. I know that the Sheet Summary was released recently (yesterday morning I believe), but I am not sure when the case specific requirements changed. I just happened to notice it for the first time yesterday, but it could have happened a week ago for all I know.

Andrée Starå

Have you contacted Smartsheet Support about it?

Paul Newcome

I have heard back from Support.

Apparently case insensitivity is something that has been around for a while now. I just never noticed it because most of my builds that required case sensitivity used the only case sensitive function. FIND.

So here is a summary of what I found out.

Using an = as a comparison in not case sensitive. CONTAINS is also not case sensitive.

The only way to ensure case sensitivity is to use the FIND function. Other than that... Case doesn't matter.

I also confirmed that this has been the case (ba-dum-bum) since 2016 for the majority of function, Feb of 2018 for the MATCH function, and of course July 2019 (release of) for the CONTAINS function.

Apparently the tests I ran that I relied on case sensitivity in the past that did not use the FIND function, just so happened to work out perfectly so that I got the expected result even though the actual process was wrong.

Long story short I was converting letters to numbers using an INDEX/MATCH. Instead of testing individual letters (like I should have), I was using entire strings of letter/number combinations that just so happened to coincidentally work out. Of course it did. This is me we're talking about here. Haha. I take one little tiny shortcut, and I end up paying for it every time.

So now that I have been "schooled" on my mistake (thanks Kara ) I will be submitting a Product Enhancement Request to ask for more case sensitivity. It was mentioned that (at least with the MATCH function's change in 2018) it was as a fix to a bug, so I won't hold my breath, but hey... It can't hurt to ask. Haha.

Side note: I am still going to save this thread as a bookmark. Andree's meme is from one of my favorite scenes in one of my favorite movies.

"shhhshushhubalaflubalubahflaablhlaulblaheehe" (I think that's the general idea)

Andrée Starå

Thanks for sharing, Paul.

I agree that it's one of the best movies from Mr. Rubberface, Jim Carrey.

haha

https://www.imdb.com/title/tt0119528/

Paul Newcome

Always trying to help share knowledge.

And I swear more than just his face is made out of rubber. I've seen some of the crazy shapes he's contorted himself into just for a good laugh. Between his other movies and some of his standup/talk show skits (not to mention Saturday Night Live when it was actually good)... I didn't think some of what he did was even physically possible. Hahaha

Andrée Starå

@Paul.

Did you see him on Seinfelds, Comedians in cars getting coffe?

Paul Newcome

I didn't! I guess I know what I'm doing on my lunch break now! Hahaha

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.