How to return cells contents if row contains specific keywords/ texts to a different cells.

[umairaziz]

I have a spreadsheet (please find attached), where in I want to pull out all Specific cells containing certain text to a whole new column. The idea is that certain values which should be found in one column are spread in multiple columns. thus i want to bring them all under one column.. Thank you!!

File could be downloaded from below link (Sorry I believe i am unable to attach directly to the post)

https://drive.google.com/uc?id=1JbJtUHA9HVudKu4v99-3n-dkW7XrI69Q&export=download

 

[Eric W]

Like so?

	D	E	F	G	H	I	J	K	L	M
1	Heading 4	Heading 5	Heading 6	Heading 7	Heading 8	Heading 9	Heading 10	Return all 11 here	Return all 17 here
2	4	5	6	7	8	9	10	www. 11	.com17
3	5	6	7	8	9	10	www. 11	www. 11	.com17
4	6	7	8	9	10	www. 11	12	www. 11b	.com17
5	7	8	9	10	www. 11b	12	13	www. 11	.com17
6	8	9	10	www. 11	12	13	14	www. 11	.com17
7	9	10	www. 11	12	13	14	15	www. 11C	.com17
8	10	www. 11C	12	13	14	15	16	www. 11	.com17
9	www. 11	12	13	14	15	16	.com17	www. 11E	.com17
10	12	13	14	15	16	.com17	18	www. 11	.com17
11	13	14	15	16	.com17	18	19	www. 11	.com17
12	14	15	16	.com17	18	19	20
13	15	16	.com17	18	19	20	21
14	16	.com17	18	19	20	21	22
15	.com17	18	19	20	21	22	23
16	18	19	20	21	22	23	24
17	19	20	21	22	23	24	25
18	20	21	22	23	24	25	26
19
20	22	23	24	25	26	27	28

<tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula K2 {=IFERROR(INDIRECT(TEXT(SMALL(IF(ISNUMBER(SEARCH("www.",$A$2:$J$20)),ROW($A$2:$J$20)*100+COLUMN($A$2:$J$20)),ROWS(K$2:K2)),"R00C00"),0),"")} L2 {=IFERROR(INDIRECT(TEXT(SMALL(IF(ISNUMBER(SEARCH(".com",$A$2:$J$20)),ROW($A$2:$J$20)*100+COLUMN($A$2:$J$20)),ROWS(K$2:K2)),"R00C00"),0),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

There are ways to change the order that the items appear, or to eliminate duplicates.