Home

how to filter special characters in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-744327%22%20slang%3D%22en-US%22%3Ehow%20to%20filter%20special%20characters%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-744327%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone%20%2C%26nbsp%3B%3C%2FP%3E%3CP%3Ecan%20anyone%20guide%20me%20how%20i%20can%20filter%20special%20characters%20in%20excel%2C%20for%20eg%20.%26nbsp%3B%20%C5%9ETI%26nbsp%3BT%C4%B0C.%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-744327%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Especial%20characters%20in%20Excel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-744402%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20filter%20special%20characters%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-744402%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Hasnain%2C%3C%2FP%3E%3CP%3EYou%20can%20use%20'custom%20filter'%20option%20available%20in%20filter%20option%20to%20find%20text%20with%20special%20characters.%3C%2FP%3E%3CP%3EYou%20just%20need%20to%20place%26nbsp%3B~%20before%20the%20special%20character%20you%20want%20to%20filter.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-744574%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20filter%20special%20characters%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-744574%22%20slang%3D%22en-US%22%3EHello%20Tauqeer%20%2C%3CBR%20%2F%3EThank%20you%20for%20swift%20reply%20%2C%20but%20seems%20this%20doesn't%20work%20with%20my%20criteria.%3CBR%20%2F%3EI%20have%20more%20than%2010%2C000%20rows%20from%20which%20i%20need%20to%20detect%20those%20columns%20only%20which%20contain%20anything%20except%20English%20Alphabets%20.%3CBR%20%2F%3EEG.%20ALT%C4%B0NTAS%20NAKL%C4%B0YE%20VE%20V%C4%B0NC%20%C4%B0SLETMEC%C4%B0L%C4%B0G%C4%B0%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-744698%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20filter%20special%20characters%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-744698%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F373219%22%3E%40mhasna%20target%3D_blankin2000%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20use%20Advance%20Filter%3C%2FP%3E%0A%3CP%3EFor%20such%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20589px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F122702iC316A511E202E2A4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ekeep%20B1%20empty%20and%20in%20B2%20formula%3C%2FP%3E%0A%3CPRE%3E%3D(SUMPRODUCT(--(UNICODE(MID(A2%2CROW(INDIRECT(%221%3A%22%26amp%3BLEN(A2)))%2C1))%26gt%3B%3D132))%26gt%3B0)%3C%2FPRE%3E%0A%3CP%3Ewhich%20checks%20if%20in%20text%20are%20any%20non-ascii%20characters%20-%20UNICODE()%26gt%3B%3D132%3C%2FP%3E%0A%3CP%3EAfter%20that%20select%20your%20entire%20range%20in%20column%20A%2C%20Data-%26gt%3BAdvanced%20Filter%20and%20here%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20292px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F122703iE5D5D58D3125C3EE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EYou%20may%20filter%20in%20place%2C%20but%20better%20to%20copy%20filtered%20data%20into%20new%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
mhasnain2000
New Contributor

Hello Everyone , 

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

 

thank you. 

3 Replies
Highlighted

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

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

@mhasnain2000 

You may use Advance Filter

For such sample

image.png

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

image.png

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

Related Conversations
Formula to calculate between "Closing Balances"
calof1 in Excel on
1 Replies
Userform-Transferred Data must not be editable
Mr_Buttons in Excel on
0 Replies
need urgent help for excel
talikne610 in Excel on
0 Replies
Excel no longer capable of Relative links
GoodallS in Excel on
0 Replies
Grouping by dates in Excel?
MMH11 in Excel on
0 Replies
Creating Pie charts.......
dougler2020 in Excel on
4 Replies