Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Find If x Text String In Column

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Find If x Text String In Column

    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

  • #2
    Re: Searching Text From Cell Contents

    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

    Comment


    • #3
      Re: Searching Text From Cell Contents

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

      Comment


      • #4
        Re: Searching Text From Cell Contents

        Perhaps

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

        Richard

        Comment


        • #5
          Re: Searching Text From Cell Contents

          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"

          Comment


          • #6
            Re: Searching Text From Cell Contents

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

            Comment


            • #7
              Re: Searching Text From Cell Contents

              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.

              Comment


              • #8
                Re: Searching Text From Cell Contents

                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")))
                .

                Comment


                • #9
                  Re: Searching Text From Cell Contents

                  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

                  Comment


                  • #10
                    Re: Searching Text From Cell Contents

                    Originally posted by 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"
                    And if 2 or 3 exist?

                    Comment


                    • #11
                      Re: Find If x Text String In Column

                      Thanks again for your responses. I used the formula from BTC2 and it worked fine. I will take a look on the other formula suggested to learn more about it.

                      Comment

                      Trending

                      Collapse

                      There are no results that meet this criteria.

                      Working...
                      X