Find in column A all values outside text 1, text 2, text 3, text 4. In B, write the values

[Slavio]

Hi, in column A I have different values formatted as text.
In column B, I want to get values from column A, but without specific texts.
Column D contains the texts I will be looking for.
I've tried different formulas, but I can't leave out more specific texts.

Find-Results.xlsx
	A	B	C	D
1	Text1	656OIuz	desired results in column B	Text1
2	656OIuz	Text2	656OIuz	Text2
3	Text2	Text3	klsdgjk5689	Text3
4	Text3	klsdgjk5689	789789879	Text4
5	klsdgjk5689	789789879	65669
6	789789879	Text4
7	Text4	65669
8	65669	0
Sheet1

Cell Formulas
Range	Formula
B1:B1048575	B1	=FILTER(A:A,A:A<>"Text1")
Dynamic array formulas.

Thanks.
Slavio

 

[awoohaw]

Try this:

Book1
	A	B	C	D
1	Text1	656OIuz	desired results in column B	Text1
2	656OIuz	klsdgjk5689	656OIuz	Text2
3	Text2	7.9E+08	klsdgjk5689	Text3
4	Text3	65669	789789879	Text4
5	klsdgjk5689		65669
6	789789879
7	Text4
8	65669
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=FILTER($A$1:$A$8,($A$1:$A$8<>$D1)*($A$1:$A$8<>$D2)*($A$1:$A$8<>$D3)*($A$1:$A$8<>$D4),"")
Dynamic array formulas.

 

[snjpverma]

awoohaw, I'm not on system.
But would like to know if below formula would also work.

=FILTER(A:A,A:A<>D1:D4)

 

[awoohaw]

awoohaw, I'm not on system.
But would like to know if below formula would also work.

=FILTER(A:A,A:A<>D1:D4)

I tried that. I'm not sure if I had the syntax correct though. It did not work.

Here is the result i get:

Book1
	F	G
1	#N/A	=FILTER($A$1:$A$8,($A$1:$A$8)<>($D$1:$D$4),"")
2
Sheet1

Cell Formulas
Range	Formula
F1	F1	=FILTER($A$1:$A$8,($A$1:$A$8)<>($D$1:$D$4),"")
G1	G1	=FORMULATEXT(F1)

 

[Slavio]

Try this:

Book1
	A	B	C	D
1	Text1	656OIuz	desired results in column B	Text1
2	656OIuz	klsdgjk5689	656OIuz	Text2
3	Text2	7.9E+08	klsdgjk5689	Text3
4	Text3	65669	789789879	Text4
5	klsdgjk5689		65669
6	789789879
7	Text4
8	65669
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=FILTER($A$1:$A$8,($A$1:$A$8<>$D1)*($A$1:$A$8<>$D2)*($A$1:$A$8<>$D3)*($A$1:$A$8<>$D4),"")
Dynamic array formulas.

@snjpverma Thanks, Your formula didn't work for me
@awoohaw

=FILTER($A$1:$A$8,($A$1:$A$8<>$D1)*($A$1:$A$8<>$D2)*($A$1:$A$8<>$D3)*($A$1:$A$8<>$D4),"")

This formula works but for some reason when I have 500 rows it skips the numeric values. I'm still checking the formatting.

 

[Fluff]

How about

=FILTER(A1:A1000,(A1:A1000<>"")*(ISNA(MATCH(A1:A1000,D1:D100,0))))

 

[Slavio]

How about

=FILTER(A1:A1000,(A1:A1000<>"")*(ISNA(MATCH(A1:A1000,D1:D100,0))))

Yes, this is what I'm looking for. Thanks

 

[Fluff]

Glad we could help & thanks for the feedback.