• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to find Non-ASCII Characters in Email?

Atul Rajratna

New Member
Hi Team,

I am looking for the formula, where I can find non-ASCII characters (non-supportive special characters) from the email address.
I tried using "=OR(IF(MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)>"a",1))" from Chandoo's forum but could not get the exact result.

We are facing lots of problem due to invalid email IDs and getting so many rejects from client end.

e.g -

á
â
ñ
ò
ø
ù
ü
ý
þ
ë
ì
Þ
ß
à

and many more..
so we can use the list of such special characters using =char()

usually we get email id's like-

m.morels@xyz.com
ve©tor@lkjh.com
tim.howârds@qwerty.com
atlasbi@žxcv.com



Here you can see special characters in the username & in the company name (excluding _-.@) which are completely non-supportive characters.
And due to high count we can't find-out such email IDs and get bounce back errors while sending email.

So please help me in this!

-Atul Rajratna
 
Hey Chihiro..

Thanks for your help.. It's working!
This is what I was looking for..

But the thing is, our agents are are not allowed to use VB and macros.
so can we do the same thing using excel formula?

Thanks again!

-Atul
 
Only way I know how, without use of VBA, you would need to build extensive list of special characters in another sheet. Then use array formula to check.

Drawback is that it will not identify any special character you haven't added to the list.

It uses dynamic named range to keep formula dynamic. However, since list uses special char, you will need helper column which will act as index for the list.

See attached for example.
 

Attachments

  • SpecialChar_List.xlsx
    18.4 KB · Views: 11
Hey Chihiro..

Can we replace these special characters with another word using same special character list?

(Not from email ID but from the first name and last name)

e.g

Client Name is "Frøland" so can we replace the specific character ø with o

ßean ---> ß with B

ãndra ---> ã with a

Chìhìro ---> both ì with i

Please help in this.

Many Thanks!
 
First add list of corresponding replacement characters in "SpecialChar" sheet.

I added it to Column D and created dynamic named range "lstReplace" (only populated few char given in example, others are all listed as "x").

To keep formula relatively short and easy to follow, I added helper columns in Email sheet.

Find: Finds first special character in Column A
Entered as array (CSE)
=IFERROR(INDEX(lstSpCh,MAX(IF(ISERROR(FIND(lstSpCh,A2)),-1,1)*(ROW(lstSpCh)-1))),"")

Replace: Looks for exact match (i.e. case sensitive) in lstReplace for the special character found above
Entered as array (CSE)
=IFERROR(INDEX(lstReplace,MATCH(1,--EXACT(lstSpCh,C2),0)),"No SpChar")

Substitute: Using results from above calculations, substitutes special character found with replacement character. Keeping original string if it has no special character.
=IF(B2=0,A2,SUBSTITUTE(A2,C2,D2))

This only finds largest index special character found in the list (ex. if there is ß and
ã present, only finds ã). Therefore, if there are more than one, you need to add additional columns to do each step using Substitute column as starting point.

Edit: Clarified last bit.
 

Attachments

  • SpecialChar_Replace.xlsx
    22 KB · Views: 12
Last edited:
Back
Top