Entry Validation

Do you mean DATA|Validation.

Here you can "force" entries within certain parameters...select custom and enter a formula like =AND(LEFT(A1,2)="A-",ISNUMBER(RIGHT(A1,7)+0),LEN(A1)=9)

this forces left 2 chars to be A- and then have only 7 digits following

where A1 is top most cell you selected to add validation to.

A big thank you for the quick response.....two quick questions.....it appears the 'A-" would be the actual data....what would one use to force a letter then the -; and the second question....if A1 is referenced in the formula......does the formula only apply to that particular cell? I need the formula to apply to all cells within a column.

Thanks,

Chris

Try:

=AND(ISERR(LEFT(A1)+0),MID(A1,2,1)="-",ISNUMBER(RIGHT(A1,7)+0),LEN(A1)=9)

Note: to apply to multiple cells, either preselect all the cells and apply the data validation, where A1 would be the top-left most cell in the selection....or.. apply in one cell and then copy|paste special validation to the other cells..the A1 will update in a relative manner.

Ran into an issue.....when I type a string consisting of any alpha characters as the "A" then the "-" followed by 7 numbers it returns an error....indicating the content is not in the proper format. Any ideas as to what I'm doing wrong? I pasted the formula into the data verification section as custom....starting as "like=.....

Chris

It worked for me...

Did you alter the formula in any way?

also, please don't quote the entire posts.. just click Post Reply instead of Quote... Thanks.

No...I pasted it as: =AND(ISERR(LEFT(A1)+0),MID(A1,2,1)="-",ISNUMBER(RIGHT(A1,7)+0),LEN(A1)=9)

Within the custom formula box.

Have a test in the attached, cell A1... are you getting errors there?

You are not entering spaces, correct?

Select the cell the data validation is working in... and then select the cells you want to copy the validation to.. go to Edit|Paste Special and select Validation... the A1 should autoupdate to the corresponding cells you pasted into.

Thank you so much for your patients.....it's working!

Chris

Originally Posted by NBVC

Select the cell the data validation is working in... and then select the cells you want to copy the validation to.. go to Edit|Paste Special and select Validation... the A1 should autoupdate to the corresponding cells you pasted into.

if it can be alphanumeric, then you probably don't need anything except then to just check the length of the whole thing is exactly what you want it to be...

so, in addition to your conditions for the first part, add condition like... LEN(A1)=13 or whatever the whole cell length should be...

again... please use POST REPLY button, not QUOTE to respond.