Validation of the First Character entered in a cell.

[jaustin]

I would like to be able to validate only the first charater entered into a
cell. I'm going to be scanning a bar code label into a cell and I need to
validate that the correct bar code data has been scanned.

Example: P12345 is scanned into cell A1. Validation needs to be done on the
first character to determine if the (P) Part number has been scanned. If the
first character is not a "P" then display an error 'You must scan the part
number label'.

Any thoughts?

 

[Jim Thomlinson]

You can use Data -> Validation -> select Custom from the list and add teh
formula

=left(A1, 1) = "P"

That validation is for data entered into cell A1.

 

[Chip Pearson]

Select the cell(s) to which you want to apply validation. Open the
Validation dialog from the Data menu. Choose "Custom" from the "Allow" list
and enter the formula

=LEFT(A1,1)="P"

Change the "A1" to the first cell of your list of cells to be validated.
This will allow either "p" or "P" as the first character. For a case
sensitive match, use

=EXACT(LEFT(A1,1),"P")


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[jaustin]

Jim: Thanks, It works great. And the response time was outstanding !!!

 

[jaustin]

ok guys, now let me throw another one at you. Once the data passes
validation, how can I remove the first character (P) from the data in the
cell?