Formula to validate characters within a string of data in aparticular cell

  • Thread starter Thread starter Mackay
  • Start date Start date
M

Mackay

Hoping you can help. I understand the use of Data Validation and how,
through formulas like Exact, I can check one cell against another cell
to confirm cells are exactly the same however I can't seem to find
reference on how to do the following.

I need to validate that names are entered correctly in a spreadsheet
which is reflecting our active directory machine name structure.
Machine names have the following breakdown:
- First 2 characters indicate the country
- Second 2 characters indicate the location
- Third 2 characters should always be "OV"
- Next 5 characters would be our contact
- Final 4 characters should be numeric.

So for example. In a worksheet I will have thousands of records that
contain entries such as:
A1 = UKMAOVExcel0001 (UK = Country, MA = Manchester, "OV", Excel =
Contact and then 4 digits).

For the country, location and contact the valid entries are stored in
a separate worksheet ("Naming"). So for example I could validate that
the machine name in A1 is correct by something like:
=VLOOKUP(LEFT(A1,2),'Naming'!A1:A800,1,FALSE) which would show me the
country and present #N/A if a invalid country has been selectedb but
how can I do this based on all of the criteria above?

Appreciate your help on this.

Thanks in advance, Al
 
Mackay brought next idea :
Hoping you can help. I understand the use of Data Validation and how,
through formulas like Exact, I can check one cell against another cell
to confirm cells are exactly the same however I can't seem to find
reference on how to do the following.

I need to validate that names are entered correctly in a spreadsheet
which is reflecting our active directory machine name structure.
Machine names have the following breakdown:
- First 2 characters indicate the country
- Second 2 characters indicate the location
- Third 2 characters should always be "OV"
- Next 5 characters would be our contact
- Final 4 characters should be numeric.

So for example. In a worksheet I will have thousands of records that
contain entries such as:
A1 = UKMAOVExcel0001 (UK = Country, MA = Manchester, "OV", Excel =
Contact and then 4 digits).

For the country, location and contact the valid entries are stored in
a separate worksheet ("Naming"). So for example I could validate that
the machine name in A1 is correct by something like:
=VLOOKUP(LEFT(A1,2),'Naming'!A1:A800,1,FALSE) which would show me the
country and present #N/A if a invalid country has been selectedb but
how can I do this based on all of the criteria above?

Appreciate your help on this.

Thanks in advance, Al
Click to expand...

Did you check out the MID() function? It will allow you to set start
position and length!
 
Back
Top