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

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

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
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies