find the phrase with a group of text in a column
 

Hello all,
In column A filled with text, i need to find the full text which contain a
phrase that is equal to cell B1 and list them all on Column C.
e.g.
A B C
SCRA-PE RAG T.RAGL-OR
T.RAGL-OR BRAG
SCRUB
FRA GILE
BRAG
FLAG


I hope someone can help me with my basics.

--
best regards,

find the phrase with a group of text in a column
 

e.g.
A B C
SCRA-PE RAG T.RAGL-OR
T.RAGL-OR BRAG
SCRUB
FRA GILE
BRAG
FLAG
--
regards,



"driller" wrote:

Hello all,
In column A filled with text, i need to find the full text which contain a
phrase that is equal to cell B1 and list them all on Column C.
e.g.
A B C
SCRA-PE RAG T.RAGL-OR
T.RAGL-OR BRAG
SCRUB
FRA GILE
BRAG
FLAG


I hope someone can help me with my basics.

--
best regards,

find the phrase with a group of text in a column
 

Pls follow following steps

1) Insert one column before column A. Now column C will become D and insert
again one column before column D. Keep row 1 blank.

Your data will be as follows now

A B C D E
================================ blank row 1
SCRA-PE RAG === row 2
T.RAGL-OR
SCRUB
FRA GILE
BRAG
FLAG
write following formula in cell a2 (I assume that your data is upto row no.
100)

=IF(ISERROR(FIND(C$2,B2)=TRUE),0,MAX(A1:A$1)+1)

copy this formula till the end (i.e. 100th row)

Now write sr. nos 1, 2 3, 4 and so on in column D. Then write following
formula in cell e2 and copy it till end

=VLOOKUP($D2,$A$1:$b$100,2,0)

This will give you result as follows

A B C D E

0 SCRA-PE RAG 1 T.RAGL-OR
1 T.RAGL-OR 2 BRAG
0 SCRUB 3 #N/A
0 FRA GILE 4 #N/A
2 BRAG 5 #N/A
0 FLAG 6 #N/A

Just change the parameter in C2 and see the result




"driller" wrote:

e.g.
A B C
SCRA-PE RAG T.RAGL-OR
T.RAGL-OR BRAG
SCRUB
FRA GILE
BRAG
FLAG
--
regards,



"driller" wrote:

Hello all,
In column A filled with text, i need to find the full text which contain a
phrase that is equal to cell B1 and list them all on Column C.
e.g.
A B C
SCRA-PE RAG T.RAGL-OR
T.RAGL-OR BRAG
SCRUB
FRA GILE
BRAG
FLAG


I hope someone can help me with my basics.

--
best regards,

find the phrase with a group of text in a column
 

thanks for the reply, i just found this basic formula and it seems to work..
on C1
'=ISNUMBER(SEARCH($b$1,A1))
and the True or false appears, and I do the filtering, same as your
suggestion.

thanks again.
--
regards,



"Prashant Runwal" wrote:

Pls follow following steps

1) Insert one column before column A. Now column C will become D and insert
again one column before column D. Keep row 1 blank.

Your data will be as follows now

A B C D E
================================ blank row 1
SCRA-PE RAG === row 2
T.RAGL-OR
SCRUB
FRA GILE
BRAG
FLAG
write following formula in cell a2 (I assume that your data is upto row no.
100)

=IF(ISERROR(FIND(C$2,B2)=TRUE),0,MAX(A1:A$1)+1)

copy this formula till the end (i.e. 100th row)

Now write sr. nos 1, 2 3, 4 and so on in column D. Then write following
formula in cell e2 and copy it till end

=VLOOKUP($D2,$A$1:$b$100,2,0)

This will give you result as follows

A B C D E

0 SCRA-PE RAG 1 T.RAGL-OR
1 T.RAGL-OR 2 BRAG
0 SCRUB 3 #N/A
0 FRA GILE 4 #N/A
2 BRAG 5 #N/A
0 FLAG 6 #N/A

Just change the parameter in C2 and see the result




"driller" wrote:

e.g.
A B C
SCRA-PE RAG T.RAGL-OR
T.RAGL-OR BRAG
SCRUB
FRA GILE
BRAG
FLAG
--
regards,



"driller" wrote:

Hello all,
In column A filled with text, i need to find the full text which contain a
phrase that is equal to cell B1 and list them all on Column C.
e.g.
A B C
SCRA-PE RAG T.RAGL-OR
T.RAGL-OR BRAG
SCRUB
FRA GILE
BRAG
FLAG


I hope someone can help me with my basics.

--
best regards,