Help With Searching For Strings In Part Numbers

[tb]

I am using Microsoft Excel 2000.

I have a spreadsheet with many, many part numbers in Column A. There
are no duplicate part numbers.

What I want to do is create a formula that will put an "X" in Column B
of a part number if one of the following strings is included in the
part numbers. (A part number will not have more than one of these
strings.)

1K0PR
1K0SI
1K0VA
1K0VR

For instance, if the part number were 1C014C014A0000D006, then there
would be no "X" in Column B because none of the above strings is
embedded in the part number. On the other hand, if the part number
were 1Z071K0VRKV0GS000AUTOM then there should be an "X" in column B
because it includes the string "1K0VR".

So, I built the following formula (which obviously does not work!!):
=IF(ISERR(SEARCH(OR("1K0PR","1K0SI","1K0VA","1K0VR"),A1,1)),"","X")

What am I doing wrong?

 

[Claus Busch]

Hi,

Am Tue, 21 Jun 2011 14:18:01 +0000 (UTC) schrieb tb:

What I want to do is create a formula that will put an "X" in Column B
of a part number if one of the following strings is included in the
part numbers. (A part number will not have more than one of these
strings.)

1K0PR
1K0SI
1K0VA
1K0VR

For instance, if the part number were 1C014C014A0000D006, then there
would be no "X" in Column B because none of the above strings is
embedded in the part number. On the other hand, if the part number
were 1Z071K0VRKV0GS000AUTOM then there should be an "X" in column B
because it includes the string "1K0VR".

try:
=IF(COUNT(SEARCH({"1K0PR";"1K0SI";"1K0VA";"1K0VR"},A1))>0,"X","")


Regards
Claus Busch

 

[Don Guillett]

I am using Microsoft Excel 2000.

I have a spreadsheet with many, many part numbers in Column A.  There
are no duplicate part numbers.

What I want to do is create a formula that will put an "X" in Column B
of a part number if one of the following strings is included in the
part numbers.  (A part number will not have more than one of these
strings.)

1K0PR
1K0SI
1K0VA
1K0VR

For instance, if the part number were 1C014C014A0000D006, then there
would be no "X" in Column B because none of the above strings is
embedded in the part number.  On the other hand, if the part number
were 1Z071K0VRKV0GS000AUTOM then there should be an "X" in column B
because it includes the string "1K0VR".

So, I built the following formula (which obviously does not work!!):
=IF(ISERR(SEARCH(OR("1K0PR","1K0SI","1K0VA","1K0VR"),A1,1)),"","X")

What am I doing wrong?

'=IF(COUNT(SEARCH({"1KOVR","1KOSI"},B28)),"X","")

 

[tb]

=IF(COUNT(SEARCH({"1K0PR";"1K0SI";"1K0VA";"1K0VR"},A1))>0,"X","")

Works great! Thanks.