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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
This website uses cookies
We use cookies to store session information to facilitate remembering your login information, to allow you to save website preferences, to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners.