Data Validation formula

[dommmm]

Hi,
I'm trying to concatenate 2 custom formulas to use in data validation.

I need to restrict:
1) text length (numbers or alpha) greater than 20 characters
2) The use of ' (apostrophe) and * (Asterix)

I have:
=IF(OR(LEN(A1)>18),FIND("*",A1))

but this wont allow any data to be typed in at all. I can make them work as individual queries but I need to have multiple validation on the same cell.

Please help.

 

[JimM]

If both conditions have to be true then try

=AND(LEN(A1)>18,FIND("*",A1))

 

[dommmm]

 

[dommmm]

View attachment 5924

think should be < as well not > but still the same regardless

 

[Toadstool]

Does this work?

=NOT(OR(LEN(A1)>20,LEN(A1)<>LEN(SUBSTITUTE(A1,"*","")),LEN(A1)<>LEN(SUBSTITUTE(A1,"'",""))))

 

[JimM]

Think the error is because your cell is probably empty, the validation should still work though if you say Yes to continue

 

[dommmm]

Does this work?

=NOT(OR(LEN(A1)>20,LEN(A1)<>LEN(SUBSTITUTE(A1,"*","")),LEN(A1)<>LEN(SUBSTITUTE(A1,"'",""))))

WOW that is brilliant and it works. I need to spend 5 mins reading through the formula though to understand it

 

[Toadstool]

WOW that is brilliant and it works. I need to spend 5 mins reading through the formula though to understand it

You're welcome!

The challenge is that an asterisk is a special character for a wildcard. Preventing an asterisk in Data Validation basically says "Don't allow anything".

SUBSTITUTE is good because if it doesn't find an asterisk it doesn't throw an error like FIND would. If there's any asterisk or apostrophe the SUBSITUTE replaces it with a null so the length will be shorter than the original text.

 

[dommmm]

You're welcome!

The challenge is that an asterisk is a special character for a wildcard. Preventing an asterisk in Data Validation basically says "Don't allow anything".

SUBSTITUTE is good because if it doesn't find an asterisk it doesn't throw an error like FIND would. If there's any asterisk or apostrophe the SUBSITUTE replaces it with a null so the length will be shorter than the original text.

Ah right, sorry if it's meant to discount the ' or * by changing them to "" then it doesn't work correctly.

If I enter "abcdefghijklmnopqrst" in A1 (20 characters) then it's fine but if I change the "o" for a "*" it gives error - it does what I need but I read that you thought it should take the * out, which would then allow me to add a "u" on the end ??

 

[Toadstool]

It's a data validation so SUBSTITUTE only removes the * or ' as a part of the validation, it won't make changes to what's entered.

I understood you wanted to restrict entry to maximum 20 characters containg no "*" or "'". Did I misunderstand?