Forum Discussion
Excel - Ignore Capitalization in Formula
I have a chart with 5 columns where users will answer a question by putting an x in one of the five columns. The columns that aren't marked with an x are to remain empty. I know that I can use Data Validation to require the x or leave it blank, but is there a way to format a formula so that it ignore capitalization? Each column will have a specified value listed in another chart with a formula that assigns that value if an x is present. But, I don't want the user to have to choose a capital or lower case x. I want either to work for my formula. Is there a way to do that?
Not to avoid answering the question, but to give you an opportunity to learn for yourself, I will tell you what I think is the answer, and then give you some advice.
I think the answer, based on a fair amount of experience, is that Excel is "smart enough' to work with either upper or lower.
Now the advice: experiment. It's what I'd have to do in order to be sure of my answer. It's a good habit to get into for your own learning of Excel. In this case, an easy experiment. Presumably you have your formulas written already, or at least an idea of what you want to be testing or doing as a consequence of an "X" or an "x" or a " " [blank]. So write the formula specifying "x" and see if "X" and "x" work interchangeably. My prediction is that they will. Either way, you have your answer and you've done it on your own.
6 Replies
Not to avoid answering the question, but to give you an opportunity to learn for yourself, I will tell you what I think is the answer, and then give you some advice.
I think the answer, based on a fair amount of experience, is that Excel is "smart enough' to work with either upper or lower.
Now the advice: experiment. It's what I'd have to do in order to be sure of my answer. It's a good habit to get into for your own learning of Excel. In this case, an easy experiment. Presumably you have your formulas written already, or at least an idea of what you want to be testing or doing as a consequence of an "X" or an "x" or a " " [blank]. So write the formula specifying "x" and see if "X" and "x" work interchangeably. My prediction is that they will. Either way, you have your answer and you've done it on your own.
- Well, I'll be dang! It does work either way! I actually decided to go another route, though, and just used the cell<>"" formula and reversed my true or false responses.
Thank you very much, Mathetes!Don't be timid about trying things out. Have fun. Play. Learn through trial and error. It's the best way to pick things up in life (OK, be prudent about the level of risk you're willing to take)...especially with Excel.
One of the reasons I have fun answering questions here in this forum is that it gives me a chance to research and learn functions that I've not used before. And from time to time, I like to encourage folks like you to relax and play a little bit in order to discover for yourself the answers to your own questions.
