Filter function to use cell values instead of formula

[Newbie73]

Hello, I'm trying to create a search box in a spreadsheet with hundreds and sometimes thousands of data:

=FILTER(B12:N2004,B12:B2004=C2)

This is what I'm using now and it works perfect IF I convert all the values of B to numbers (Values of the column B are obtained through a formula which provides then 4 digits, 1234 for example).

Is there any way to add to the Filter formula to use the numbers in the cell? I have dozens of spreadsheets that need to be updated from time to time, that's why I'm trying to avoid the converting to number option.

Can provide a small spreadsheet example if necessary and sorry if it's a stupid question.

Thanks!

 

[etaf]

can you *1
=FILTER((B12:N2004)*1,(B12:B2004)*!=C2)

or maybe
=FILTER(--(B12:B2004),--(B12:B2004)=C2)

to change the text numbers to real numbers

an example would be good to see

 

[Newbie73]

Hello, you're right so I've done a mini version of it:

test1.xlsx

Do let me know if that helps, first formula gives a error and doesn't let me use it and the second formula gives a #Value error.

Let me know if I can do anything else to help!

 

[etaf]

not following with the example posted
where is the filter function?

where is the problem
the text function will show text numbers to change to real numbers

=IF(B5<>0, RIGHT(TEXT(B5,"0000"),4)*1, "")

OR

i have added a filter

=FILTER(A5:D10,(A5:A10)*1=L2)

is that what you mean

test1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Search									Example done manually
2		a	b	c	d						Search	5780
3											a	b	c	d
4	a	b	c	d							5780	PPP745561305780	4	3
5	2594	PPP746560602594	5	9
6	5780	PPP745561305780	4	3
7	4761	PPP745561604761	5	8							5780	PPP745561305780	4	3
8	2817	PPP745561102817	3	1
9	6046	PPP745561306046	5	3
10	2566	PPP746560602566	5	3
11
12
Sheet1

Cell Formulas
Range	Formula
K7:N7	K7	=FILTER(A5:D10,(A5:A10)*1=L2)
A5:A10	A5	=IF(B5<>0, RIGHT(TEXT(B5,"0000"),4), "")
Dynamic array formulas.

 

[Newbie73]

The filter function is now in the example sheet sorry about it. The problem was with the formula in the Column it would give a #Value error but with your change to the other formula it now works! I do apologise if it was a stupid question but that change you did, did the trick

=IF(B5<>0, RIGHT(TEXT(B5,"0000"),4)*1, "")

 

[etaf]

you are welcome

various ways to do that also
=IF(B5<>0, --RIGHT(TEXT(B5,"0000"),4), "")
=IF(B5<>0, RIGHT(TEXT(B5,"0000"),4)+0, "")