validation with two specific formats

[Guest]

Hi there,

How do you make sure certain character will be showing up on Excel cell,
e.g. R123456. The first letter must be S and followed by 6 numbers?

I have come up with formula: =AND(LEFT(A1, 1)="R", LEN(A1)=7), which is not
perfect because it will accept, e.g. R65432S.

Any advice?

 

[Guest]

Try something like this:

Select the cells to be impacted, with A1 as the active cell

From the Excel main menu:
<data><validation>
Allow: Custom
Formula: =AND(COUNTIF(A1,"R??????"),MOD(RIGHT(A1,6),1)=0,--RIGHT(A1,6)>=0)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Bernard Liengme]

Try =AND(LEFT(A1)="R",ISNUMBER(--RIGHT(A1,6)))
I hope I read your question correctly (you seem to want an R not an S)
Not my formula will accept lower case r.
This accepts only uppercase
=AND(EXACT(LEFT(A1),"R"),ISNUMBER(--RIGHT(A1,6)))

 

[Guest]

Actually, there's a flaw in my posted formula...

it allowed an entry like this: R1234.0

Try this, instead:
=AND(COUNTIF(A1,"R??????"),COUNTIF(A1,"*.*")=0,MOD(RIGHT(A1,6),1)=0,--RIGHT(A1,6)>=0)

Note: That formula still allows a lowercase "r". If that is an issue,
then maybe this?:
=AND(COUNTIF(A1,"R??????"),EXACT(LEFT(A1,1),"R"),COUNTIF(A1,"*.*")=0,MOD(RIGHT(A1,6),1)=0,--RIGHT(A1,6)>=0)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Thank you so much. It works! You are very smart. Wondering where did you
learn all this.

I noticed that "--" before "RIGHT(A1,6)" is a must, why is that.

Thanks,