Excel - Ignore Capitalization in Formula

[Kim-Kay]

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?

[#2 · Accepted solution]

@Kim-Kay 

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.

[#3]

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!

[#4]

@Kim-Kay 

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.

[#5]

I have done a little bit of poking around (within reason) and I've definitely learned a lot working on this project. Thank you for the encouragement! :)

[#6]

@Kim-Kay 

Back in the day--I've never been an IT professional--I used to read the printed manuals that came with software like Lotus 1-2-3, which in many ways was the first spreadsheet to truly make personal computers must-haves on corporate desks. I'd read the manuals--skimming often--on, say, an annual basis, and almost always come up with an "Aha, THAT'S an easier way to do X"; one of the things you've already discovered, in a relatively minor way, is that there almost always are multiple ways in Excel to get from point A to point B. There are different functions,  different ways to write the formula, some less effective perhaps, but they still get the job done.

If you don't have some reference texts--if you're gong to be doing more and more with Excel -- I highly recommend you become acquainted with some of the many websites that offer solid guidance. One of my favorites is ExcelJet. There are also many solid YouTube channels worth subscribing to, where they'll actually walk you through how to implement some of the more sophisticated functions. PivotTable is a capability, for example, well worth acquainting yourself with (if you haven't already). And then there are books....You know how to research that category.

[#7]

Oh my! Thank you, thank you, thank you! I really need those references! I am trying to learn more so having good sound guidance is always appreciated.