Forum Discussion

mhasnain2000's avatar
mhasnain2000
Copper Contributor
Jul 09, 2019

how to filter special characters in Excel

Hello Everyone , 

can anyone guide me how i can filter special characters in excel, for eg .  ŞTI TİC. etc.

 

thank you. 

special characters in Excel

3 Replies

  • tauqeeracma's avatar
    tauqeeracma
    Iron Contributor
    Jul 09, 2019

    Hi Hasnain,

    You can use 'custom filter' option available in filter option to find text with special characters.

    You just need to place ~ before the special character you want to filter.

     

    Thanks

    Tauqeer

    • mhasnain2000's avatar
      mhasnain2000
      Copper Contributor
      Jul 09, 2019
      Hello Tauqeer ,
      Thank you for swift reply , but seems this doesn't work with my criteria.
      I have more than 10,000 rows from which i need to detect those columns only which contain anything except English Alphabets .
      EG. ALTİNTAS NAKLİYE VE VİNC İSLETMECİLİGİ
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jul 09, 2019

        mhasnain2000 

        You may use Advance Filter

        For such sample

        keep B1 empty and in B2 formula

        =(SUMPRODUCT(--(UNICODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))>=132))>0)

        which checks if in text are any non-ascii characters - UNICODE()>=132

        After that select your entire range in column A, Data->Advanced Filter and here

        You may filter in place, but better to copy filtered data into new column.

Resources