Data Validation

[Frank Bacchus]

Hi All:

I am using the Data Validation function to control the value and format in my C cells. here is the validation:

=(LEN(C2)=9)*(LEFT(C2,4)="ABC-")*ISNUMBER(--RIGHT(C2,5))

C2 cell will have 9 characters as follows:

First 4 characters will be "ABC-";
The next 5 characters need to be numeric.
​

The formula works well, as is but I need to change it to allow the numeric portion to be 3 or 4 or 5 numbers. Any ideas on how I can change this? Thanks in advance.

frank

 

[Chihiro]

Try...
=(LEFT(C2,4)="ABC-")*ISNUMBER(RIGHT(SUBSTITUTE(C2,"-",REPT(" ",50)),50)*1)*(LEN(TRIM(RIGHT(SUBSTITUTE(C2,"-",REPT(" ",50)),50)))>=3)

 

[Frank Bacchus]

Try...
=(LEFT(C2,4)="ABC-")*ISNUMBER(RIGHT(SUBSTITUTE(C2,"-",REPT(" ",50)),50)*1)*(LEN(TRIM(RIGHT(SUBSTITUTE(C2,"-",REPT(" ",50)),50)))>=3)

Chiro: Thanks. I am now able to enter more than 9 characters. Where does - =(LEN(C2)=9)* - go? Thanks

frank

 

[Chihiro]

You can't satisfy len 9 with less than 5 char numeric at the end. Just add len<=9 as additional condition if needed.

 

[bosco_yip]

Perhaps,

1] Testing data as per above picture

2] In D2, formula copied down :

=(LEN(C2)<10)*(LEN(C2)>6)*(LEFT(C2,4)="ABC-")*ISNUMBER(-MID(C2&".",4,99))

Regards
Bosco

 

[Frank Bacchus]

View attachment 57593

Perhaps,

1] Testing data as per above picture

2] In D2, formula copied down :

=(LEN(C2)<10)*(LEN(C2)>6)*(LEFT(C2,4)="ABC-")*ISNUMBER(-MID(C2&".",4,99))

Regards
Bosco

View attachment 57593

Perhaps,

1] Testing data as per above picture

2] In D2, formula copied down :

=(LEN(C2)<10)*(LEN(C2)>6)*(LEFT(C2,4)="ABC-")*ISNUMBER(-MID(C2&".",4,99))

Regards
Bosco

Thank all. This is working. Appreciate it.

frank