Find If x Text String In Column

[mgiv]

How do I search a given text "GNMA" from a cell (A1) and (A2) and return it in other cell (B1) and (B2).

Example:
A

1 GNMA-4565X18-ROE
2 895GNMA-12

[RichardSchollar]

Hi

Given those values in the cell, what exactly do you want the formula to return? True/False that it contains the searched for term, or something else?

Richard

[mgiv]

I want to return the text that I'm searching, in this case would be GNMA.

[RichardSchollar]

Perhaps

=IF(ISNUMBER(FIND("GNMA",A1)),"GNMA","Not Found")

Richard

[mgiv]

Thanks for your response. What if I need to search and return more than one value/text. To search if column A has any of the following 3 values/text.

"GNMA"
"FHLMC"
"FNMA"

[ByTheCringe2]

mgiv, Please describe your full question in your first post, as in the rules you have agreed to. Is post #5 the full question?

[mgiv]

No, I just undestood that my 2nd question was to go a bit further on my first question and both questions are related to the thread title that I started (as per rule #12)

Please correct me if I'm wrong, because I want to comply and your forum Has been very helpful to me.

[ByTheCringe2]

LOL, I thought it was my job to quote rule numbers! OK, but please realise that someone is going to have to re-do the formula, so unless you need the single case as well as the 3-case, you are wasting a helper's time.

Here is a formula for 3 searches:

=IF(ISNUMBER(FIND("GNMA",A1)),"GNMA",IF(ISNUMBER(FIND("FHLMC",A1)),"FHLMC",IF(ISNUMBER(FIND("FNMA",A1)),"FNMA","Not Found")))

[RichardSchollar]

I think the following is unnecessarily complicated, but until Domenic, Kris or DaddyLongLegs turns up I can't think of anything better - assume your list of lookup words is in F1:F3 then in the B column (eg starting B1, copied down):

=IF(MAX(COUNTIF(A1,"*"&$F$1:$F$3&"*")),OFFSET($F$1,MAX(ROW($F$1:$F$3)*COUNTIF(A1,"*"&$F$1:$F$3&"*"))-1,0),"Not Found")

Confirmed with Ctrl+Shift+Enter (it's an array formula - Excel will surround with curly braces {} if entered correctly - do not enter these yourself manuall).

Richard

[Dave Hawley]

Thanks for your response. What if I need to search and return more than one value/text. To search if column A has any of the following 3 values/text.

"GNMA"
"FHLMC"
"FNMA"

And if 2 or 3 exist?