Data validation with wildcards

Poco90

New Member
Joined
Jan 20, 2005
Messages
23
I hope someone can help with a problem that is driving me soft? I want to enter a 15 digit part number into a cell and compare it to up to 5 different 15 digit part numbers (on the same sheet) and return a yes or no in an adjacent cell if there is a match or not to these 5. My problem is that ocaisionally an extra random digit or letter is put on the end of the part number. Could anyone suggest a formula for me please? I have being trying IF THEN statements but have had no luck. Thanks in advance,
Poco
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi Poco,

The formula is a bit long and tedius, but you could use:

=IF(OR(MID(A1,1,15)="183946301745528",MID(A1,1,15)="394687430255289",MID(A1,1,15)="987654321098765",MID(A1,1,15)="123456789012345"),"Yes","No")

Damon
 

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi again Poco,

I should also mention that with a few lines of VBA code it would be possible to "validate" the referenced cell (or range of cells) so that it would not be possible to enter a part number in the cell that is inconsistent with your criteria. If you would like the code for this, let me know.

Damon
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,021
Office Version
  1. 365
Why not use = A1&"5" instead of manually typing the 15 digits?
 

KenWright

Active Member
Joined
Jan 14, 2005
Messages
267

ADVERTISEMENT

With myrange being your 5 valid values and myval being the reference to your lookup value

=COUNTIF(myrange,myval&"*")

Addendum

Sorry, forgot the Yes/No bit
=IF(COUNTIF(myrange,myval&"*")>0,"Yes","No")
 

Poco90

New Member
Joined
Jan 20, 2005
Messages
23
Just want to thank you all for your responses. I was going nowhere fast with my efforts. Thanks again,
Poco
 

Poco90

New Member
Joined
Jan 20, 2005
Messages
23

ADVERTISEMENT

I think I need to be spoon fed or shot.

Damon
when I use your code for only numbers it works fine, but it doesn't seem to like my part numbers. I should have made it clearer to you that the suppliers product code is alpha numeric and may have spaces in the middle

for example
MNRIE14E0ABEJ220
MNRIE14E0AB J220
MCRIE03EZHEJ3R0
MCRIE03EZPEJ3R0
MCRIE03EZH J3R0

The list above is the range that I would call suitable for cell A1. The other problem I have is even though these are the genuine supplier part numbers, when we get the supplier part the label says MNRIE14E0ABEJ220 but it actually reads as MNRIE14E0ABJ220 J (I am inputting these parts with a barcode scanner)

I modified your code to =IF(OR(MID(A1,1,15)="MNRIETR14E0ABJ220",MID(A1,1,15)="MNRIE14E0AB J220",MID(A1,1,15)="MCRIE03EZPEJ3R0",MID(A1,1,15)="MCRIE03EZHEJ3R0"),"Yes","No") but I keep getting no.

Do you have any suggestions? If the VBA code is the way to go and the offer is still there, I'd would like to take you up on it if its not too much trouble? I know even less about VB than I know about excel.



Ken
When I try your code I get also get a no. I uses A5:A9 as my range and A1 as myval. Is there any way I (should say you) could modify your code for my part no.?

Thank you all again for your time and effort. Best regards,
Poco
 

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi Poco90,

The problem has nothing to do with numbers vs. characters, but rather in the fact that you appear to be using 16-digit part numbers rather than the 15 you mentioned in your problem statement. Either that, or the zero at the end of each of your strings is the "extra random digit" you mentioned. If the zero at the end is truly part of the part number you want to match and therefore some of the part numbers are 16 digits long, you must use 16 in the corresponding MID function, like this:

=IF(OR(MID(A1,1,16)="MNRIE14E0ABEJ220",MID(A1,1,16)="MNRIE14E0AB J220",MID(A1,1,15)="MCRIE03EZHEJ3R0",MID(A1,1,15)="MCRIE03EZPEJ3R0",MID(A1,1,15)="MCRIE03EZH J3R0"),"Yes","No")

where the last three are compared to 15 characters because they are 15 characters long.

If, on the other hand, the zero of the 16-character part numbers IS NOT part of the part number you want to compare (is an extra random digit), then do the compare against the part numbers without the zeros:

=IF(OR(MID(A1,1,15)="MNRIE14E0ABEJ22",MID(A1,1,15)="MNRIE14E0AB J22",MID(A1,1,15)="MCRIE03EZHEJ3R0",MID(A1,1,15)="MCRIE03EZPEJ3R0",MID(A1,1,15)="MCRIE03EZH J3R0"),"Yes","No")

Note that the space does count as a character, as you presumed.

You also mentioned that MNRIE14E0ABEJ220 scans as MNRIE14E0ABJ220 J, which not only has an extra two characters at the end, but also is missing the E. If the " J" is what you refer to as an extra random character, the MID function in the formula should get rid of it, but the missing E is a problem. To match this you should have

MID(A1,1,15)="MNRIE14E0ABJ220"

in the formula, i.e., comparing it to the part number without the E.

Does this help?

Damon
 

Poco90

New Member
Joined
Jan 20, 2005
Messages
23
Damon,
I will have to admit defeat. The parts I am scanning are throwing up too many variants, multiple spaces in the part number as well as the random character on the end of the number so I think this is my problem. Thank you again for all your input. Rgds,
Poco
 

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi again Poco90,

What is not possible with worksheet formulas is often easily accomplished with a VBA user-defined function (UDF). If the problem is being able to tolerate extra spaces inserted anywhere in the string as well as a random character on the end, this can be accomplished via a UDF. Would you like to give this approach a try?

Damon
 

Forum statistics

Threads
1,148,056
Messages
5,744,548
Members
423,882
Latest member
Seeham

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top