Thread: Data VAlidation - Text Length & Character Type

Hi Jim,

Try this as a custom validation for cell A1.

=(LEN(A1) < 10) * ISERROR(FIND("$",A1))  * ISERROR(FIND("%",A1))

Hi Jim

I tackle these problems one at a time, one cell at a time.
I looked down the Validation List and saw Custom and decided it must be a custom rule.

I started with cell A1 and put Len(A1) < 10 and Excel didn't like it. I put a = in front of it and it worked. Trial and Error, Got Lucky.
So Validation is looking for a equal in front (like Conditional Formatting) that it can check for True or False to accept.

Then in another Cell I tried Find("$",A1) and it came back #Value. Bummer. So I put a =IsError in front of it and it became TRUE. I did the same for % and just built up the formula using ANDs. That didn't work so I remembered somewhere the Times means AND and Plus means OR. I changed the AND to "*" and got lucky.

So the final answer is it only returns True if it is (True * True * True). I think its more of watching the brilliant gurus on this board that gives me a step up.

I used to teach high school math. In the section of "Word Problems" I preached you must guess an answer and build formulas by discovering what didn't work. As soon as you have something that doesn't work, you are closer to an answer. Finally after some wrong guesses and a little frustration you arrive at a formula. With the formula you could then solve it to find the final answer.

See http://en.wikipedia.org/wiki/How_to_Solve_It

There was this HUGE problem for many students, even for good students, as they didn't want to guess a wrong answer. They just wanted the formula where they plugged in the variables, turned the crank and out poped (or pooped) the answer. I found that there was this attribute for people called Gumption or Fortitude or Confidence that allowed them to accept a wrong answer to get closer to the final answer.

I hope this post showed how to guess wrongly and keep going with smaller parts of the puzzle to arrive at a formula. I've been watching DonkeyOte (i'm not worthy) build some unbelieveable CSE formulas. I'm sure he builds them a small peice at a time with trial and error. I'll get there someday.