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
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