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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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
51
DanteAmor
DanteAmor
O
  • Question Question
IF cell contains text found in another column
Replies
2
Views
354
kvsrinivasamurthy
K
A
  • Solved
Conditional Formatting - apply rules to multiple rows based on specific cells (drop down menus)
Replies
4
Views
560
AndyB86
A
P
  • Solved
VBA - Clear one cell if another one changes in the same row
Replies
7
Views
442
Alex Blakenburg
A
R
  • Solved
Using conditional formatting to highlight the row of a cell containing specific text.
Replies
4
Views
2K
etaf
etaf

Forum statistics

Threads
1,223,897
Messages
6,175,270
Members
452,628
Latest member
dd2

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