Contents[Hide]

Using given list of latname and firstname of German users, split them to separate fields, change umlauts to their eqivalents and create email address. Then import users as chatter free.

Example list of given data:

  A
1 Full Name
2 Smith, John
3 Nowak,  James
4 Müller, Thomas

 

Second user name contains two spaces, third one has german "ü" which should be changed for ue to create email address This email address is being protected from spambots. You need JavaScript enabled to view it.

1. Excel formulas which helps us prepare data.

Extract firstname:

=TRIM(MID(A2;FIND(",";A2)+1;1000))

Extract Lastname:

=TRIM(LEFT(A2;FIND(",";A2)-1))

Where:
FIND is searching for comma
MID extract 1000 characters from given position
LEFT extract all characters from beggining of a string up to given character position. -1 removes comma.
TRIM removes spaces before and after string.


Replace German national characters in firstname or lastname

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D6;"ä";"ae");"ö";"oe");"ü";"ue");"ß";"ss")

Create email address:

=LOWER(CONCATENATE(E2;".";F2;"@";"mycompany.com"))

Alias (first letter of firstname + 4 letters from lastname):

=CONCATENATE((LEFT(C2;1));LEFT(D2;4))

CommunityNickname:

=LOWER(CONCATENATE(E2;".";F2))

2. Other reqired fields

EmailEncodingKey = UTF-8
TimeZoneSidKey = Europe/Berlin
LocaleSidKey = de_DE_EURO
LanguageLocaleKey = de
DefaultCurrencyIsoCode = EUR
ProfileId = yourPID
Username = email
 

   
© Paweł Woźniak