Thanks:  0
Likes:  0

# Thread: Finding a cell in a range containing the required fragment

1. ## 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. ## 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

3. ## Re: Finding a cell in a range containing the required fragment

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

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

4. ## 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. ## Re: Finding a cell in a range containing the required fragment

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