# Search a word in a text string in cells in a table

#### Nordik

##### New Member
Hi,

Let's say I have a result of a survey, but the answers got mixed up... some cells moved away form their column:

 Dog Fish Cat Bob I have a dog - blue I have a fish - yellow I have a cat - black Carl I have a cat - grey I have a dog - red I have a fish - black John I have a cat - red I have a fish - red I have a dog - green Anna I have a fish - blue I have a dog - yellow I have a cat - white

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

I tried to sort this by using FIND, MATCH, INDEX but I seems I cannot make my formula work. For each line, I was trying to find "dog" for example and then display in a new column the content of cell in which "dog" is found. The final result I am looking for is:

 Dog Fish Cat Bob I have a dog - blue I have a fish -yellow I have a cat - black Carl I have a dog - red I have a fish - black I have a cat - grey John I have a dog - green I have a fish - red I have a cat - red Anna I have a dog - yellow I have a fish - blue I have a cat - white

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

I don't need a formula to sort the whole table at once, I can do column by column/several steps.

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
See if this works for you

Code:
https://app.box.com/s/c5cs8rbf5inq3ly47zce

Give this formula a try (put it in e2 and copy it right to G2 and down as long as required):

=INDEX(\$B2:\$D2,MATCH("*"&B\$1&"*",\$B2:\$D2,0))
Excel Workbook
ABCDEFG
1dogfishcatdogfishcat
2BobI have a dog - blueI have a fish - yellowI have a cat - blackI have a dog - blueI have a fish - yellowI have a cat - black
3CarlI have a cat - greyI have a dog - redI have a fish - blackI have a dog - redI have a fish - blackI have a cat - grey
4JohnI have a cat - redI have a fish - redI have a dog - greenI have a dog - greenI have a fish - redI have a cat - red
5AnnaI have a fish - blueI have a dog - yellowI have a cat - whiteI have a dog - yellowI have a fish - blueI have a cat - white
Sheet

Thanks to both of you.
Your solutions are working like a charm

=IF(IFERROR(FIND(LOWER(B\$1);\$B2);0);\$B2;IF(IFERROR(FIND(LOWER(B\$1);\$C2);0);\$C2;IF(IFERROR(FIND(LOWER(B\$1);\$D2);0);\$D2;0)))

=INDEX(\$B2:\$D2;MATCH("*"&B\$1&"*";\$B2:\$D2;0))

I will go with István's formula as it was the one I tried to create. I wasn't using the * and the & in the correct way.

Thanks again

Replies
3
Views
438
Replies
1
Views
157
Replies
5
Views
308
Replies
13
Views
439
Replies
4
Views
258

1,203,075
Messages
6,053,391
Members
444,661
Latest member
liamoohay

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back