Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 21st 11, 03:18 PM posted to microsoft.public.excel.worksheet.functions
tb tb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2011
Posts: 6
Default Help With Searching For Strings In Part Numbers

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?
--
tb

  #2   Report Post  
Old June 21st 11, 03:43 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,788
Default Help With Searching For Strings In Part Numbers

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #3   Report Post  
Old June 21st 11, 03:51 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2010
Posts: 1,522
Default Help With Searching For Strings In Part Numbers

On Jun 21, 9:18*am, "tb" wrote:
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?
--
tb


'=IF(COUNT(SEARCH({"1KOVR","1KOSI"},B28)),"X","")
  #4   Report Post  
Old June 21st 11, 04:22 PM posted to microsoft.public.excel.worksheet.functions
tb tb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2011
Posts: 6
Default Help With Searching For Strings In Part Numbers

On 6/21/2011 at 9:43:09 AM Claus Busch wrote:

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


Works great! Thanks.
--
tb


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Searching for strings in a column [email protected] Excel Worksheet Functions 6 January 23rd 09 06:20 PM
vba searching for strings in multiple worksheets [email protected] Excel Discussion (Misc queries) 2 July 26th 06 07:15 PM
Searching for codes in text strings Bhupinder Rayat Excel Worksheet Functions 9 April 27th 06 01:20 PM
searching for multiple text strings eddie Excel Discussion (Misc queries) 4 April 10th 05 10:15 PM
Searching for Substrings Within Strings Tiziano Excel Discussion (Misc queries) 8 January 6th 05 03:09 AM


All times are GMT +1. The time now is 09:18 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017