Search, find or lookup defined text in text string
 

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

Search, find or lookup defined text in text string
 

Try:

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

"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?
--
Thanks

Search, find or lookup defined text in text string
 

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


"Toppers" wrote:

Try:

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

"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?
--
Thanks

Search, find or lookup defined text in text string
 

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

Search, find or lookup defined text in text string
 

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


"zzxxcc" wrote:

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


"Toppers" wrote:

Try:

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

"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?
--
Thanks

Search, find or lookup defined text in text string
 

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

Search, find or lookup defined text in text string
 

Ron's formula worked fine for me.

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

"zzxxcc" wrote:

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

Search, find or lookup defined text in text string
 

On Thu, 6 Sep 2007 06:16:02 -0700, zzxxcc
wrote:

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

Search, find or lookup defined text in text string
 

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 !
--
Thanks


Ron Rosenfeld skrev:

On Thu, 6 Sep 2007 06:16:02 -0700, zzxxcc
wrote:

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

Search, find or lookup defined text in text string
 

On Thu, 6 Sep 2007 12:46:05 -0700, zzxxcc
wrote:

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 !
--
Thanks

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