Search, find or lookup defined text in text string

[Guest]

My data in B2:B1000 contains text and numbers. In D21000 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?

 

[Guest]

Try:

=IF(ISNUMBER(FIND("FCM",B2)),MID(B2,FIND("FCM",B2),3),"")

 

[Guest]

That din't work. The cell contained "......FCO....", but returned "FCM"

 

[Ron Rosenfeld]

My data in B2:B1000 contains text and numbers. In D21000 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,"*"&TextStrings&"*")>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

 

[Guest]

Maybe we could combine with VLOOKUP and put the FCM, FCN, FCO.... in a table.
I have 12 different "words" to look up.

 

[Guest]

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

 

[Guest]

Ron's formula worked fine for me.

Did you enter with Ctrl+Shift+Enter? If not, you will gt #NA errors.

 

[Ron Rosenfeld]

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

--

It will return #N/A if:

1. Did you enter the string segments into a NAME'd range? One thing I left
out is that the range must be vertical. If the range is horizontal, then the
formula needs a slight modification.

2. Do you see the {...} around the formula put there by Excel? If not, you
did not enter the formula properly as instructed:

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.

3. The strings in TextStrings are not present in your data.

What do you see?
--ron

 

[Guest]

Thank you Ron and Toppers. This worked fine !
The mistake I made was forgetting ctrl+shift+enter.
For reference I also need to replace the "," with ";" as my computer is set
up as North European.

Obviously I need to learn more about Array formulas. It's the first time I
have seen the { } in a formula.

You saved my day !

 

[Ron Rosenfeld]

Thank you Ron and Toppers. This worked fine !
The mistake I made was forgetting ctrl+shift+enter.
For reference I also need to replace the "," with ";" as my computer is set
up as North European.

Obviously I need to learn more about Array formulas. It's the first time I
have seen the { } in a formula.

You saved my day !

Glad you got it working. Thanks for the feedback.
--ron