How to generate email address from names by 1-replacing local letters and 2-adding ".@..."

[1tane1]

Hello,
I want to create a vba code with would have three missions:
1) Replace local characters into english letters. I keep these characters in A1:B6 such as replace A1 to B1, A2 to B2 etc. (I cant write replace(name, "İ", "I") because excel doesn't recognize local letters but I can use excel cells to replace)
ǻC
ݻI
лG
Ö»O
޻S
ܻU

2) small all characters and get rid of spaces.
3) add this to the end: "[email protected]"

examples:
C columns to D columns
ÖMER ŞİMŞİRÇİ » [email protected]
OĞUZ ÇÖP KÜLLÜĞ » [email protected]

[Pepe Le Mokko]

Are the local characters added manually or imported from another file?

[Haluk]

If the names are in cells starting from A1 to down .... the below code will generate the e-mail addresses as your request.

Please Login or Register  to view this content.

[1tane1]

Haluk Bey,
Thank you for your simple and shortcut solution. It works great. Could you tell me the secret of 64?
It works great.

[Pepe Le Mokko]

Excuse my ignorance but how does that macro satisfy the two first requirements?

Replace local characters into english letters. I keep these characters in A1:B6 such as replace A1 to B1, A2 to B2 etc. (I cant write replace(name, "İ", "I") because excel doesn't recognize local letters but I can use excel cells to replace)
ǻC
ݻI
лG
Ö»O
޻S
ܻU

2) get rid of spaces.

[1tane1]

" ", "" this part removes the spaces but I couldn't understand how the formula transforms local characters into English characters and I guess 64 makes that magic somehow. Maybe it removes dots from the local characters.

[bakerman2]

64 is the numeric value of vbUnicode in the strConv-function.

[Haluk]

Problem of converting some of the Turkish characters to nearest readable ASCII character is always discussed in our local Turkish Excel forums.

The solution I suggested in message #3 was proposed by me 2 years ago, which can be seen from the link below...


https://www.excel.web.tr/threads/nok...17/post-952691


As you know, "StrConv" function's parameters are;

Please Login or Register  to view this content.

where, LCID stands for "Local ID" for language/country settings. Values that can be used are listed in the link below;

https://analystcave.com/vba-referenc...onv-function/#


When I attemted to use this function to solve the problem, I started playing around to find a suitable LCID and found that, some of them were giving good results.

Such as; (Ukrainian - 1058) or (Tajik - 1064)

Then, I just tried some values that are not listed in the LCID table to see what's going on. Most of them ended with an error message as expected but some of them did not throw any errors but the results were not suitable. Such as using; 98 or 32 ... Thus, I thought that if a value not listed in the LCID table does not give an error but returns an unsatisfactory result, then there may be a value which doesn't give an error, returning a satisfactory result.

At the end, I found the "magic" number 64 by trial-and-error. May be I could have found some more values also but I don't remember as it was 2 years ago.

This is the story of the code on my end ...

.

[1tane1]

I'm very lucky that you saw my question and I learnt something new from you. Cool story
I also thank to the others who attempt to help, for your time and interest.

[Haluk]

You're welcome (rica ederim).

.