Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation based on digits in the middle of a string
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation based on digits in the middle of a string
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. "TexasStar" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation based on digits in the middle of a string
On Apr 20, 2:02 pm, TexasStar wrote:
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation based on digits in the middle of a string
On Apr 20, 1:45 pm, David G wrote:
On Apr 20, 2:02 pm, TexasStar wrote: 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 Thx for the suggestions everyone. I will try these out and let you know how it goes. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation based on digits in the middle of a string
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation based on digits in the middle of a string
On Apr 20, 3:55 pm, TexasStar wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add a character to the middle of a text string | Excel Discussion (Misc queries) | |||
Forcing a line feed in the middle of a string | Excel Discussion (Misc queries) | |||
How to extract text from middle of a string | Excel Worksheet Functions | |||
Copy middle digits from one cell into another | Excel Worksheet Functions | |||
copy middle digits from one cell into another | Excel Discussion (Misc queries) |