Création de login à partir d’une liste d’employés avec Excel

Il arrive parfois qu’à partir d’une base de données importée (genre CSV)  nous ayons le besoin de créer des login en grande quantité à partir de noms et prénoms.

Le login est généralement composé de la première lettre du prénom séparé par un point du nom.

Exemple pour : Aline Durand cela devient le login : a.durand

Si le prénom est composé on prendra les deux premières lettres.

Exemple pour : Marie-Catherine Durand cela deviendra : mc.durand

S’il y a deux noms de famille il sera séparé par un tiret également.

Exemple pour : Anne-Sophie Dupont Demoncorget cela deviendra : as.dupont-demoncorget

N’oublions pas non plus que tout cela doit être écrit en minuscules.

Nous allons procéder par étape afin de comprendre comment nous arriverons à notre résultat.

Comment récupérer la première lettre d’une cellule.

Pour cela nous allons utiliser la fonction STXT, fonction qui renvoie un nombre donné de caractères extraits d’une chaîne de texte à partir de la position que vous avez spécifiée, en fonction du nombre de caractères spécifiés.

=stxt (Texte de départ ; n° de départ d’extraction ; nombre de caractères à extraire)

Exemple de STXT simple :

Comment mettre en majuscule ou en minuscule en combinant avec le STXT.

Combinons sur nom prénom et concaténons avec le &. (concaténer = mettre ensemble)

Comment remplacer les espaces par des tirets

Dans toutes les bases de données vous trouverez des prénoms composés qui seront écrits soit avec un tiret soit avec un espace. Utilisons SUBSTITUE fonction qui permet de remplacer un texte spécifique dans une chaîne de caractères.

Ce qui nous donne pour l’instant la formule suivante et on approche !

=MINUSCULE(STXT(B2;1;1))& ». »&MINUSCULE(SUBSTITUE(A2; » « ; »-« ))

Mais il nous reste le problème des prénoms composés que nous allons traiter avec trois fonctions. N’oublions pas que pour les prénoms composés on note les deux premières lettres.

Nous allons imaginez que vous avez utilisé la fonction SUBSTITUE pour remplacer tous les espaces par des tirets dans la colonne des prénoms.

Afficher la  lettre qui suit le trait d’union avec les fonctions si ; nb.si ; cherche.

On va d’abord donner un exemple simple pour chaque fonction et ensuite nous compléterons notre formule.

La fonction si (simple) :

=SI(test logique ;valeur si la réponse est vrai ;valeur si la réponse est fausse)

Exemple : Il y a 5000 en B43 et 2000 en B44 ; la formule inscrite dit que si la cellule est supérieure à 2500 je veux voir « oui » sinon je veux voir « non »

La fonction NB.SI :

Compte le nombre de cellules d’une plage qui répondent à un critère spécifique que vous spécifiez. Vous pouvez, par exemple, compter toutes les cellules qui commencent par une lettre donnée ou compter toutes les cellules qui contiennent un nombre supérieur ou inférieur à un nombre donné.

Exemple : compter le nombre de fois où je trouve le mot « marseille » dans cette liste

=NB.SI(plage ;critère)

Ici on va simplement détourner un peu cette fonction pour lui demander de regarder s’il y a un trait d’union dans notre cellule

=NB.SI(B2; »*-* »)

Maintenant il nous faut rajouter la lettre qui va suivre ce trait d’union qu’Excel aura trouvé dans la cellule. Pour cela nous allons utiliser la troisième fonction : CHERCHE

La fonction CHERCHE :

Renvoie le numéro du caractère au niveau duquel un caractère spécifique ou une chaîne de texte est initialement reconnu(e) à partir du n° de départ. Utilisez la fonction CHERCHE pour déterminer la position d’un caractère ou d’une chaîne de texte dans une autre chaîne de texte de façon à pouvoir utiliser ensuite les fonctions STXT ou REMPLACER pour modifier le texte.

=CHERCHE(texte_cherché;texte;N°de départ)

Remplaçons le texte par nos données :

=STXT(B2;CHERCHE(« -« ;B2)+1;1)

On ne rajoute pas de numéro de départ, cela signifie que l’on commence dès le début.

On a ajouté 1 à la fonction CHERCHE, car la fonction nous donne la position du trait d’union, et on veut la lettre qui suit ce trait d’union.

Voici nos 3  fonctions réunies pour afficher la lettre qui suit le trait d’union.

Notre formule terminée donne :

En couleur pour plus de compréhension.

=MINUSCULE(STXT(B2;1;1))&MINUSCULE(SI(NB.SI(B2; »*-* »);STXT(B2;CHERCHE(« -« ;B2)+1;1);))& ». »&MINUSCULE(SUBSTITUE(A2; » « ; »-« ))

Et si l’on veut à partir du login terminé en colonne C ici on peut restreindre le nombre de caractères si la situation le demande.

Exemple le login ne doit pas dépasser 12 caractères dans une nouvelle colonne on écrira notre formule stxt en la concaténant avec le « @ »et le « fai .fr » par exemple.

=STXT(C2;1;12)& »@fai.fr »

Ce qui donne pour le premier login : [email protected]

Pour les plus intéressés :

Pour ceux qui veulent en apprendre un peu plus et donc connaitre le fonctionnement de chaque fonction utilisée, je joins le classeur Excel dans lequel vous aurez d’autres petits bonus, d’autres fractionnement.