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

U

umairaziz

New Member
Joined
Aug 6, 2016
Messages
3
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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
Like so?


DEFGHIJKLM
Heading 4Heading 5Heading 6Heading 7Heading 8Heading 9Heading 10
www. 11
www. 11
www. 11b
6www. 11
www. 11
www. 11C
www. 11.com17
16
.com17
.com1720
.com17
.com17
.com17

<tbody>
[TD="align: center"]1[/TD]

[TD="bgcolor: #DCE6F1"]Return all 11 here[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] "]Return all 17 here[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="bgcolor: #DCE6F1"]www. 11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] "].com17[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]

[TD="bgcolor: #DCE6F1"]www. 11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] "].com17[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]12[/TD]
[TD="bgcolor: #DCE6F1"]www. 11b[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] "].com17[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]
[TD="bgcolor: #DCE6F1"]www. 11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] "].com17[/TD]
[TD="align: right"][/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="bgcolor: #DCE6F1"]www. 11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] "].com17[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="bgcolor: #DCE6F1"]www. 11C[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] "].com17[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]16[/TD]
[TD="bgcolor: #DCE6F1"]www. 11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] "].com17[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]16[/TD]

[TD="bgcolor: #DCE6F1"]www. 11E[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] "].com17[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]

[TD="align: right"].com17[/TD]
[TD="bgcolor: #DCE6F1"]18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] "]www. 11[/TD]
[TD="align: right"].com17[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]16[/TD]

[TD="align: right"]18[/TD]
[TD="align: right"]19[/TD]
[TD="bgcolor: #DCE6F1"]www. 11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] "].com17[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]16[/TD]

[TD="align: right"]18[/TD]
[TD="align: right"]19[/TD]

[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] "][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]16[/TD]

[TD="align: right"]18[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]21[/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] "][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]16[/TD]

[TD="align: right"]18[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]22[/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] "][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]18[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]23[/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] , align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]24[/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] , align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]25[/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] , align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]26[/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] , align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]28[/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] , align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K2[/TH]
[TD="align: left"]{=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),"")}[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L2[/TH]
[TD="align: left"]{=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),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]


There are ways to change the order that the items appear, or to eliminate duplicates.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Search a column for cell contents, in any order
Replies
3
Views
65
DanteAmor
DanteAmor
O
  • Question Question
IF cell contains text found in another column
Replies
2
Views
431
kvsrinivasamurthy
K
A
  • Solved
Conditional Formatting - apply rules to multiple rows based on specific cells (drop down menus)
Replies
4
Views
759
AndyB86
A
E
  • Question Question
How to Highlight Each Column-Heading of the Active Row When The Corresponding Cells Contain 'X'
Replies
4
Views
153
Peter_SSs
Peter_SSs
P
  • Solved
VBA - Clear one cell if another one changes in the same row
Replies
7
Views
470
Alex Blakenburg
A

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top