two Conditional formatting

[faizee]

allow to enter value in cell, only if
1. data contain 1 time "x" value between two numeric value . & both numeric values should not be more than 99,
e.g
34x45.5, (allow)......
21x56.44 (allow)
23c45.5, (not allow).
23 x 45.5 (any space not allow)
101x22 (numeric more than 99 not allow)

 

[shinigamilight]

Will only work for conditional formatting.

=IF(AND(ISERROR(SEARCH(" ",B1)),IFERROR(SEARCH("""x",B1),0),RIGHT(B1,1)=""""),NOT(AND(VALUE(REPLACE(B1,SEARCH("""x",B1),95,""))<=99,VALUE(SUBSTITUTE(REPLACE(B1,1,1+SEARCH("""x",B1),""),"""",""))<=99)),NOT(IFERROR(AND(VALUE(REPLACE(B1,SEARCH("x",B1),95,""))<=99,VALUE(REPLACE(B1,1,SEARCH("x",B1),""))<=99,SEARCH("x",B1)>=1,ISERROR(SEARCH(" ",B1))),FALSE)))

 

[faizee]

no. i mean any single value with inverted comma is also allowed
e.g 24" or 24.5"

34x45.5, (allow)......
21x56.44 (allow)
23c45.5, (not allow).
23 x 45.5 (any space not allow)
101x22 (numeric more than 99 not allow)
23" (allow)
23.4" (allow)
222" (more than 99 not allow)
23"x33" (two inverted comma value not allow)
23"x33 (not allow)

 

[shinigamilight]

=NOT(IFERROR(IFERROR(AND(REPLACE(B1,SEARCH("""",B1),95,"")+0<=99,RIGHT(B1,1)="""",SUMPRODUCT(--(MID(B1,ROW($A$1:$A$20),1)=""""))=1),AND(VALUE(REPLACE(B1,SEARCH("x",B1),95,""))<=99,VALUE(REPLACE(B1,1,SEARCH("x",B1),""))<=99,SEARCH("x",B1)>=1,ISERROR(SEARCH(" ",B1)))),FALSE))

 

[faizee]

thanks for reply. but it should not look values in $A$1:$A$20, it should only see value to its relevant row.
e.g. see only inverted comma value in its relevant row. 24" or 24.5"

 

[faizee]

hello
can you please guide

 

[shinigamilight]

thanks for reply. but it should not look values in $A$1:$A$20, it should only see value to its relevant row.
e.g. see only inverted comma value in its relevant row. 24" or 24.5"

Did you even try and input the formula? The row a1:a20 is for calculation to get number from 1 to 20, it's not looking for values in a1:a20. I'm not gonna reply to this anymore. Good luck