formula Finding text and returning certian word

Anandpersad

New Member
Joined
Oct 18, 2014
Messages
31
Hi All,

I am looking for a formula for the following:
I have a list of word, (FOV1, FOV2, FOV3, FOV4, FOV5, FOV6, FOV7, FOV8 and FOV9), which I would to find in a range of text in column A and have the searched text (FOV1 or FOV2 or FOV3 or FOV4 orFOV5 or FOV6 or FOV7, FOV8 or FOV9), returned in column B.
Like in “column A” I have the range of text. In column B I would like to put a formula, which will search for the 9 words as mentioned above, and give me which of the FOV are in text in column A. The text which I am looking for in column A, can be anywhere in the text range. There is also not a recurrent word or character which I use to find my texts.

I tried the following formula:
=IF(ISNUMBER(SEARCH("FOV1",A2)),"FOV1",IF(ISNUMBER(SEARCH("FOV2",A2)),"FOV2",IF(ISNUMBER(SEARCH("FOV3",A2)),"FOV3",IF(ISNUMBER(SEARCH("FOV4",A2)),"FOV4",IF(ISNUMBER(SEARCH("FOV5",A2)),"FOV5",IF(ISNUMBER(SEARCH("FOV",A2)),"FOV6","FOV7"))))))

As you this formula is restricted to max 7. By FOV7, I get the error message that more levels of nesting are used than allowed (I have more than 7 FOV).

Can you help me with a formula, if possible?

Thanks and Regards,

Anand
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
If you using Excel version 2007 or higher and it is not in compatibility mode you should not experience this type of problem. Here is a formula that should work for you.

enter formula in B2 and copy down.

=IFERROR(LOOKUP(10^308,SEARCH({"FOV1","FOV2","FOV3","FOV4","FOV5","FOV6","FOV7","FOV8","FOV9"},A2),{"FOV1","FOV2","FOV3","FOV4","FOV5","FOV6","FOV7","FOV8","FOV9"}),"")
 
Last edited:
Upvote 0
Hi Alkey,

Thank you very much for your quick reply and help.
Formula works perfectly for me.

Anand



If you using Excel version 2007 or higher and it is not in compatibility mode you should not experience this type of problem. Here is a formula that should work for you.

enter formula in B2 and copy down.

=IFERROR(LOOKUP(10^308,SEARCH({"FOV1","FOV2","FOV3","FOV4","FOV5","FOV6","FOV7","FOV8","FOV9"},A2),{"FOV1","FOV2","FOV3","FOV4","FOV5","FOV6","FOV7","FOV8","FOV9"}),"")
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top