Validation based on digits in the middle of a string

[TexasStar]

Here is the scenerio,

I have a number like this 30-015-00745 in cells A2 to A150. The
important part of that number is the 3 middle digits. Example: 015

I have another page that lists several hundred 3 digit combinations.
Example: 015

How can I validate that the 3 middle numbers exist in the list on page
2 where I store all the possible 3 digit combinations that are
allowed?

I have created a named range for the 3 digit numbers on page 2 because
I know the built in validator doesn't like going to a different page
to get a list to validate against. So I created a name range called
"validlist".

Is it possible to perform this validation on the three digits in the
middle of the string 30-015-00745 and if so how might I go about
doing that validation?

 

[Joel]

This will extract the middle digits from a number that contains two dashes.
It may look strange, but it works.

=MID(F25,FIND("-",F25)+1,FIND("-",F25,FIND("-",F25)+1)-FIND("-",F25)-1)

you can put this function into a VLOOKUP.

 

[David G]

Is it possible to perform this validation on the three digits in the
middle of the string 30-015-00745 and if so how might I go about
doing that validation?

You can add data validation to your cells from Data > Validation...
Specify Custom validation type and use a formula along the lines of:
=AND(NOT(ISERROR(SEARCH("-???-",A1))),NOT(ISNA(MATCH(MID(A1,SEARCH("-???-",A1)+1,3),validlist,
0))))
Where A1 needs to be changed to the address of the cell to be
validated.

Quick explanation:
1. SEARCH finds the index of the middle section in the string format
you specified
2. MID cuts out the 3 characters between the -'s
3. MATCH tried to find that value in the list you specified. It
returns #N/A if the value is not found

Be sure to provide a more specific error message than the default data
validation error message, or the users will go crazy.

Good luck,
David

 

[TexasStar]

You can add data validation to your cells from Data > Validation...
Specify Custom validation type and use a formula along the lines of:
=AND(NOT(ISERROR(SEARCH("-???-",A1))),NOT(ISNA(MATCH(MID(A1,SEARCH("-???-",­A1)+1,3),validlist,
0))))
Where A1 needs to be changed to the address of the cell to be
validated.

Quick explanation:
1. SEARCH finds the index of the middle section in the string format
you specified
2. MID cuts out the 3 characters between the -'s
3. MATCH tried to find that value in the list you specified. It
returns #N/A if the value is not found

Be sure to provide a more specific error message than the default data
validation error message, or the users will go crazy.

Good luck,
David

Thx for the suggestions everyone. I will try these out and let you
know how it goes.

 

[TexasStar]

David,

I tried using your code in the data validation part and when I copy
and paste it in and try and hit the "ok" button it gives me an error
message back saying that the forrmula contains an error. I tried to
figure out where the problem is but this seems like a fairly complex
formula and I wans't able to fix the problem. Can you look your
formula over and see if you can figure out where the error is?

I think your method is exactly what I am looking for so it would be
really awesome if I can get this working! Thx for your help with this.
It's greatly appreciated.

 

[David G]

David,

I tried using your code in the data validation part and when I copy
and paste it in and try and hit the "ok" button it gives me an error
message back saying that the forrmula contains an error. I tried to
figure out where the problem is but this seems like a fairly complex
formula and I wans't able to fix the problem. Can you look your
formula over and see if you can figure out where the error is?

I think your method is exactly what I am looking for so it would be
really awesome if I can get this working! Thx for your help with this.
It's greatly appreciated.

Did you change A1 to the address of the cell you are setting up the
validation for?
Did you paste the entire formula (including the = sign)?
Are you using Excel 2003 or higher (I'm not sure if lower versions
support all the functions)?

Here are some debugging steps you could use:
1. Try pasting the formula into a regular cell and see if it displays
TRUE or FALSE.
2. Try breaking up the formula into smaller bits to see if they work
=SEARCH("-???-",A1) // should show the index of the -
###- part for text in A1
=MID(A1,SEARCH("-???-",A1)+1,3) // should extract the
middle portion of the text in A1
=MATCH(MID(A1,SEARCH("-???-",A1)+1,3),validlist,0) //
should print a number if the middle portion is found, or an error if
not

Unfortunately theres only so much I can do without seeing the error
that you're seeing.

David