Validation

[rhmkrmi]

Hello,

I am trying to create a validation for a specific syntax.

Example: BP:402865


The BP: is fixed but the 6-digit number varies.

Could you please help with a formula that I can use in the validation?


Thank you

 

[rajeshksreedharan]

use the below formula in Data-Data Validation-Custom

=LEFT(A1,3)="BP:"

 

[rhmkrmi]

I thought of that but what if the user enters BP: 502479?
The should be no space between the colon and the number.

Thanks

 

[Fluff]

Not totally foolproof, but how about
=AND(LEFT(A2,3)="BP:",LEN(A2)=9,ISNUMBER(--RIGHT(A2,6)))

 

[rhmkrmi]

Thank you very much.

 

[Fluff]

You're welcome & thanks for the feedback

 

[rhmkrmi]

Is it possible to have more than one data validation in a cell as formula?

I am trying to use custom data validation for one cell with both of:

=AND(NOT(EXACT(G38,LOWER(G38))),NOT(EXACT(G38,UPPER(G38))),ISERROR(SEARCH("–",G38,1)),R38="School")
=AND(EXACT(G39,UPPER(G39)),ISERROR(SEARCH("–",G39,1)),R39="Home")

Thank you.

 

[Joe4]

If it gets too complex, it may be necessary to use VBA. You can code a Worksheet_Change event, which is VBA code that runs automatically upon manual updates of specified cells.
In that code, you could add as many checks as you like.

There are some example out there, like these:
https://www.exceltrainingvideos.com...entry-values-in-an-excel-worksheet-using-vba/
http://www.java2s.com/Code/VBA-Excel-Access-Word/Excel/ValidatingdataentryinWorksheetchangeevent.htm