Is there a way to make an if formula case sensitive?

Hunter Vaught · 04/14/21

I need an if function to evaluate to true only if the cell it is checking is in all caps, but right now it evaluates to true if it is lowercase, is there a formula to do this? Thank you!

Paul Newcome · 04/15/21

Hmm... This is something I had started on a while back but had to put on the back burner because it wasn't a top priority for me.

My initial thought was parsing out the text string, but that isn't very scalable and still presents some hurdles. I do know that the FIND function is case sensitive, so (untested) maybe we could try something like converting it to all upper case using the UPPER function then saying if the FIND function finds the upper case string in the original string, then the original string must be all upper case.

Feel free to give it a shot and let me know how it works out. I'm not sure why I didn't think of it before. I will try to test on my own sometime later this evening.

=IF(FIND(UPPER([Text String Column]@row), [Test String Column]@row) > 0, 1)

Paul Newcome · 04/14/21

Are you able to provide some examples?

Hunter Vaught · 04/14/21

https://community.smartsheet.com/discussion/comment/287395#Comment_287395

Ex: If I had

=IF([Sample]@row = "TESTING", 1, 0)

then both "testing" and "TESTING" would make the formula evaluate to true, putting a check in the checkbox, I only want it to evaluate to true if the cell contains "TESTING"

Paul Newcome · 04/15/21

Hmm... This is something I had started on a while back but had to put on the back burner because it wasn't a top priority for me.

My initial thought was parsing out the text string, but that isn't very scalable and still presents some hurdles. I do know that the FIND function is case sensitive, so (untested) maybe we could try something like converting it to all upper case using the UPPER function then saying if the FIND function finds the upper case string in the original string, then the original string must be all upper case.

Feel free to give it a shot and let me know how it works out. I'm not sure why I didn't think of it before. I will try to test on my own sometime later this evening.

=IF(FIND(UPPER([Text String Column]@row), [Test String Column]@row) > 0, 1)

Hunter Vaught · 04/15/21

https://community.smartsheet.com/discussion/comment/287470#Comment_287470

Thank you!!! This worked perfectly, I didn't need to use the UPPER function, but using IF(FIND() > 0, 1) solved my problem.

Paul Newcome · 04/15/21

Can you provide the exact formula you used? Did you type a "text string" into the FIND function, or use a cell reference?

Hunter Vaught · 04/15/21

=IF(FIND("sample test string", [Sample]@row) > 0, 1, 0)

This is the formula I used, I changed the text string and the name of the column. To answer your question I did use a "text string"

Paul Newcome · 04/15/21

Ah. Ok. That's why it worked without the UPPER function. Since you entered the specific text string, you didn't have to convert anything as you were able to control that when you entered it. Awesome!

Thanks for asking the question as I had completely forgotten about this issue and you helped me think of a solution I hadn't previously thought of.

Is there a way to make an if formula case sensitive?

Best Answer

Answers

Help Article Resources

Categories