Results 1 to 5 of 5
Like Tree1Likes
  • 1 Post By 63falcondude

Finding a cell in a range containing the required fragment

This is a discussion on Finding a cell in a range containing the required fragment within the Excel Questions forums, part of the Question Forums category; I get a row of cells containing some text. Let's say they are names. Mary Jane White John Robert Black ...

  1. #1
    New Member
    Join Date
    Apr 2017
    Posts
    3

    Default Finding a cell in a range containing the required fragment

    I get a row of cells containing some text. Let's say they are names.

    Mary Jane White John Robert Black Susan Lou Brown Roger Green Alice Ann Grey Peter Red




    With an Excel formula I need to get the whole content of the cell that contain a given word. For example, if I get "Ann", I should be able to return "Alice Ann Grey".

    Any suggestions would be greatly appreciated. Thank you.

  2. #2
    Board Regular 63falcondude's Avatar
    Join Date
    Jan 2016
    Posts
    3,149

    Default Re: Finding a cell in a range containing the required fragment

    Try something along the lines of =INDEX(A1:F1,MATCH("*"&A5&"*",A1:F1,0))

    where "Ann" is in A5
    Ramzay likes this.

  3. #3
    New Member
    Join Date
    Apr 2017
    Posts
    3

    Default Re: Finding a cell in a range containing the required fragment

    Quote Originally Posted by 63falcondude View Post
    Try something along the lines of =INDEX(A1:F1,MATCH("*"&A5&"*",A1:F1,0))

    where "Ann" is in A5
    Thanks. That worked beautifully.

  4. #4
    Board Regular
    Join Date
    Oct 2013
    Location
    Lakeland, FL USA
    Posts
    290

    Default Re: Finding a cell in a range containing the required fragment

    Here is another way
    Enter formula in B5 and copy down

    =IF(A5="","",LOOKUP(1,-FIND($A5,A$1:F$1),A$1))

    ABCDEF
    1Mary Jane WhiteJohn Robert BlackSusan Lou BrownRoger GreenAlice Ann GreyPeter Red
    2
    3
    4
    5AnnAlice Ann Grey
    6BlackJohn Robert Black
    7GreeRoger Green
    8SusSusan Lou Brown
    9RedPeter Red

    Sheet5




  5. #5
    Board Regular 63falcondude's Avatar
    Join Date
    Jan 2016
    Posts
    3,149

    Default Re: Finding a cell in a range containing the required fragment

    Quote Originally Posted by Ramzay View Post
    Thanks. That worked beautifully.
    You're welcome. Happy to help.

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com