Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Find If x Text String In Column

  1. #1
    Join Date
    19th May 2006
    Posts
    41

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th September 2005
    Location
    Hampshire, UK
    Posts
    1,278

    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    19th May 2006
    Posts
    41

    Re: Searching Text From Cell Contents

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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    18th September 2005
    Location
    Hampshire, UK
    Posts
    1,278

    Re: Searching Text From Cell Contents

    Perhaps

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

    Richard

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    19th May 2006
    Posts
    41

    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"

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

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

  7. #7
    Join Date
    19th May 2006
    Posts
    41

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

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

  9. #9
    Join Date
    18th September 2005
    Location
    Hampshire, UK
    Posts
    1,278

    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

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,714

    Re: Searching Text From Cell Contents

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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 2
    Last Post: January 23rd, 2008, 02:16
  2. Find numbers within text string
    By Lippa in forum EXCEL HELP
    Replies: 6
    Last Post: August 9th, 2006, 18:16
  3. web and excel find text string...
    By luca2005 in forum EXCEL HELP
    Replies: 2
    Last Post: May 22nd, 2006, 15:20
  4. [Solved] Formulas: Find string within text - pt2
    By Dorian in forum EXCEL HELP
    Replies: 7
    Last Post: October 8th, 2003, 00:54
  5. Formulas: Find string within text
    By Dorian in forum EXCEL HELP
    Replies: 3
    Last Post: September 23rd, 2003, 19:39

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno