Select only numbers in several columns cells

[sameeradissanayake]

I have excell chart and want to filter only numbers of all columns. how can i do this.


Capture 1.JPG

Thanks
sam

[José Augusto]

Hi

Suppose your data in A2: D12 (line one has headers)

Use in E2 the following formula (copy down)
=INDEX(--OR(A2:D2=$E$1),,)
For example, set E1 as 22 and filter column E by 1
All lines in which 22 does not appear will be hidden

Alternatively
Concatenate all the cells of a row with this formula to get the column E (copy down)
=A2&"-"&B2&"-"&C2&"-"&D2&"-"
You can now filter through column E under Text Filter in the search box.

[sameeradissanayake]

Hi José Augusto

Thanks for the attention on my issue, but i want to show all the numbers together. we can't write a formula for a specific number.
there should not be any empty cells in a column.

Thanks
Sam

[José Augusto]

Hi José Augusto

... i want to show all the numbers together. ...
Sam

All numbers in one column?
Try this in J2 and drag down

Please Login or Register  to view this content.

[GeoffW283]

Or is this the result you are looking for?

scrunch.png

If so then the following macro will do it for you:

Please Login or Register  to view this content.