Is there a way to make an if formula case sensitive?
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!
Best Answer
-
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)
Answers
-
Are you able to provide some examples?
-
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"
-
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)
-
Thank you!!! This worked perfectly, I didn't need to use the UPPER function, but using IF(FIND() > 0, 1) solved my problem.
-
Can you provide the exact formula you used? Did you type a "text string" into the FIND function, or use a cell reference?
-
=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"
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 443 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!