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

@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
2 Seniority Lists
Aminam20 in Excel on
2 Replies
Conditional formatting with wildcard
Rajeev_Raghavan in Excel on
2 Replies
Inconsistency error in the MROUND function
Fleischbender in Excel on
1 Replies
#OVERLOOP!
MarcAdank in Excel on
1 Replies
INDEX MATCH function, avoid duplicate returns
certavi in Excel on
3 Replies