I'm sorry, Ron. This returns #N/A where it should return: FCM. However, I
think you are on to something.

--
Thanks


"Ron Rosenfeld" wrote:

On Thu, 6 Sep 2007 03:14:15 -0700, zzxxcc
wrote:

My data in B2:B1000 contains text and numbers. In D2:D1000 I need to return
the findings of a search in B2. Eksample: The text in B2 is "Add FCM#6". I
want to search B2 for text containing FCM, FCN, FCO, FCP, ect. If I find one
of these combinations I want the found text to be returned in D2. In this
case: "FCM".

I am familiar with: =SEARCH("FCM",B2) - but this only returns the number
"5".
I am familiar with: =MID(B2,5,3) - but the number 5 in the formula is
fixed. Sometimes the text I search starts at position 5, sometimes at pos. 6,
10, 15,...-not fixed.
How can I combine these two functions with OR or some other relevant function?

Try this:

In some area on your worksheet, enter in separate cells the text strings you
are searching for. NAME that range "TextStrings".

Then, enter this formula in D2:

=INDEX(TextStrings,MATCH(TRUE,COUNTIF(B2,"*"&TextS trings&"*")0,0))

When you ENTER this formula, you must hold down <ctrl<shift while hitting
<enter as this is an ARRAY formula. Excel will place braces {...} around the
formula.

Then fill down to D1000.
--ron