Search for value within a string

[JimDandy]

I have a list of device names in one column which includes a model number. I have another worksheet where the list of available model numbers reside. I'd like to use the list of model numbers to search against the first list to find all those in the list that contain the model number.

The first worksheet would contain items such as this:

Intel(R) Core(TM)2 Duo CPU T7500 @ 2.20GHz
Intel(R) Pentium(R) M processor 1600MHz
Genuine Intel(R) CPU T2300 @ 1.66GHz

The second worksheet contains a series of model numbers such as this:

T5500
T7200
T7400
T7500
T7600

I want to insert a row in the first worksheet that shows "Yes" if the item's model number is located in the second worksheet and "No" if it's not. The above example should result in a Yes, No and No. The problem is that the value I'm looking for can exist anywhere in the string on the first worksheet.

[stanleydgromjr]

JimDandy,

What is the information for the following:

Intel(R) Core(TM)2 Duo CPU T7500 @ 2.20GHz
sheetname?
column?
beginning cell of the raw data in the column?
column to put the yes or no?


T5500
sheetname?
column?
beginning cell of the raw data in the column?

[6StringJazzer]

The following user-defined function will do this. The first argument is the full description string and the second parameter is the range containing the model numbers.

Please Login or Register  to view this content.

It assumes that the model number is surrounded by blanks in the description string.

You would use it something like this:

=WordFound(A1,Sheet2!A1:A50)

It returns TRUE and FALSE. You could modify the code or just use this formula:

=IF(WordFound(A1,Sheet2!A1:A50),"Yes","No")

[Marcol]

Similar to 6-string but without UDF

Using Ron Coderres' post earlier tonight

In Sheet1 B2

Please Login or Register  to view this content.

Drag/Fill Down

And adding
In C2

Please Login or Register  to view this content.

Drag/Fill Down

Copy and PasteSpecial > Values if you don't need the formulae, but it might be handy to retain them in case either list changes

Keep the sumproduct range to a reasonable minimum to avoid overworking, the demo can use up to 100 rows of model numbers.

Hope this helps

[6StringJazzer]

<issue with attachment has been resolved>

I am having a hard time deciphering this part of your formula

Please Login or Register  to view this content.

and figuring out how in the world it is doing the string matching. I am thinking that the literal arrays contain wildcards, but still can't figure out how it works.

Can you provide a bit of an explanation?

[Marcol]

Attachment seems to down-load okay now.

Best see Rons' explanation here, and the disscussion about it
http://social.answers.microsoft.com/...4-d3caf203fb28

Basically
The three sets test for
Begins with: "xxx *"
Contains: "* xxx *"
Ends with: "* xxx"

Read it like so

{"","* " ,"* " }&Sheet2!$A$1:$A$100&{ " *", " *" ,""}

[DonkeyOte]

Regards TRUE/FALSE

Please Login or Register  to view this content.

would be another alternative.

[Marcol]

@ DonkeyOte
Neat "trick", adding the leading and trailing spaces to the strings, saving the array as used by Ron

Would using a Dynamic Named Range be a significant advantage with either formula?

ModelNo
Refers to:

Please Login or Register  to view this content.

Then either

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

I can see it being an advantage with sumproduct, calculation time wise.

[DonkeyOte]

With both approaches (as presented) you'd have to be sure to exclude blanks from the criteria range to avoid errors.

Using a Dynamic Name would obviously allow for a dynamic list but would not (necessarily) avoid the pitfalls of blanks if they were interspersed.

In terms of accounting for that possibility specifically with the SEARCH method:

Please Login or Register  to view this content.

[JimDandy]

The UDF worked great on most of the data but anytime there was a digit or letter designation in the model name (on Sheet1) that matched any digit or letter anywhere in the list (on Sheet2) it resulted in a True or Yes condition.

In other words, neither a 4 nor an M are in the list individually on Sheet2 however there is a 3, 4 and 5 character model number with a 4 in it as well as some with M in it, so both these conditions returned true. It appears that it's not a whole word match. I tested this by changing all the 4's and M's on Sheet2 to something else and any model with a 4 or M then returned False.

These returned True when they should be False:

Intel(R) Pentium(R) 4 CPU 3.00GHz
Intel(R) Pentium(R) M processor 1600MHz

[6StringJazzer]

Can you post your data? I didn't have much to use for a test case.

[JimDandy]

My apologies to all those that provided resolutions, I was sidetracked by another project and failed to get back to this. I will mark this as solved until such time as I need to pick this back up.

[William Okumu]

You can use SEARCH function for example =if(iserror(search("t7500",first wksheet description,1),"NO","YES"))