Filter

[sdsurzh]

Hi,


Suppose in A COLUMN contains both text as well as number how I can filter only text (without using multiple option from dropdown).


Thanks,

Suresh Kumar S

 

[Faseeh]

Hi Suresh,


You can use Text Filter > Begins With Option.


Faseeh

 

[NARAYANK991]

Hi Suresh ,


If you can use a helper column , enter the following formula , in the helper column :


=ASC(A1)=A1


where your data starts from A1.


The above formula will return TRUE for text , and FALSE for numbers. Alternatively , you can use :


=TYPE(A1)


which will return 1 for numbers and 2 for text.


Filter on this helper column.


Narayan

 

[Debraj]

Hi Suresh,


Do you allow me to do the same in two steps...


* Select column.

* Select only Numbers.. (ALT + E + G + S + O + U) Go To > Special > Constant > Only Number..

* OK.. and set any color to the selected cells..

* Now apply filter by color..


Regards,

Deb

 

[sdsurzh]

Thanks Faseeh,Narayan & Deb

 

[anupamtiwari05]

Hi Suresh,


You can also try below:


Just select your column A and Press Alt+HLN key combination and select option "Use formula to format cell".


write any one of the below formula:


=ISTEXT(A1)=True , and select color to format.


Or


=Type(A1)=2 , and select color to format.


The above will highlight cells containing text.


Now you can apply filter by color as said by Deb.


P.S. Just need favor from Deb to know how to apply filter using color, as honestly speaking, I don't know how to do it.


Thanks & Regards,

Anupam Tiwari

 

[sdsurzh]

Thanks Anupam Tiwari & Kris

 

[Krishnakumar]

Hi


Select 'Contains' and the criteria "*" (without quotes)


for Numbers, 'Does not contain' and *


Kris

 

[anupamtiwari05]

Hi Kris,


Thanks

 

[Debraj]

Hi Anupam..

[pre]

=ISERROR("OOPS.. I just realise.. you are using <= 2003.. my fault.. :(", _
"Apply Filter > Filter by Color.."

[/pre]
PS: oops same mistake. again.. you dont have ISERROR also..


@Krishna..

Nice TRICKS admin...

I tried to be smart and apply "?*" to ignore blank cells from Numberic search LOL.. and you know the rest.. but anyway.. learned a lot from you..


Regards,

Deb

 

[anupamtiwari05]

Many Thanks Deb,


I got it.


Regards,

Anupam Tiwari