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?

--
Aline Yiu

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


"Aline Yiu" wrote:

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?

--
Aline Yiu

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"),C OUNTIF(A1,"*.*")=0,MOD(RIGHT(A1,6),1)=0,--RIGHT(A1,6)=0)

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

XL2002, WinXP


"Ron Coderre" wrote:

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


"Aline Yiu" wrote:

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?

--
Aline Yiu

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)))
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Aline Yiu" wrote in message
...
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?

--
Aline Yiu

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,
--
Aline Yiu


"Bernard Liengme" wrote:

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)))
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Aline Yiu" wrote in message
...
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?

--
Aline Yiu