Lance a un ensemble de noms dans un classeur. Les noms sont tous dans la colonne A, et certains ont le prénom et le nom, tandis que d’autres utilisent le prénom, le deuxième prénom et le nom de famille. Il doit séparer les noms en colonnes individuelles, mais l’assistant Texte en colonnes ne fournit pas de résultats satisfaisants. Cela fait bien la séparation, mais le problème de deux contre trois noms signifie que Lance doit faire beaucoup de massages manuels des données une fois qu’elles sont séparées.

La solution au problème consiste à ne pas compter sur l’assistant Texte en colonnes, mais à utiliser à la place un certain nombre de formules pour obtenir les noms dans des colonnes. Les résultats que vous obtenez dépendent toujours, en grande partie, de l’état des données que vous analysez. Si vos données sont au format « premier milieu dernier » (le deuxième prénom étant facultatif), vous pouvez utiliser la formule suivante pour extraire le prénom:

=LEFT(A1,SEARCH(" ",A1,1)-1)

Cette formule vérifie le premier espace dans le nom, puis suppose que tout ce qui précède cet espace est le prénom. La formule suivante est utilisée pour déterminer s’il y a un deuxième prénom, et s’il y en a un, l’afficher:

=IF(ISERROR(SEARCH(" ", RIGHT(A1, LEN(A1) - SEARCH(" ", A1, 1)), 1)) = TRUE, "", LEFT( RIGHT(A1,LEN(A1) - SEARCH(" ", A1, 1)), SEARCH(" ", RIGHT(A1, LEN(A1) - SEARCH(" ", A1, 1)), 1) - 1))

Même si la formule apparaît ici sous forme de 5 lignes, il s’agit en réalité d’une formule unique et doit être saisie comme telle. La formule vérifie l’existence d’un deuxième espace dans le nom. Si une erreur est générée (il n’y a pas de deuxième espace), la formule ne renvoie rien; il n’y a pas de deuxième prénom. Si un deuxième espace est détecté (aucune erreur n’est générée), alors la formule renvoie tout ce qui se trouve après le premier espace jusqu’au deuxième espace.

La formule suivante est utilisée pour extraire le nom de famille:

=RIGHT(A1,LEN(A1)-LEN(B1)-LEN(C1)-IF(C1="",1,2))

Cette formule repose sur les résultats renvoyés pour le prénom et le deuxième prénom. Il renvoie tout ce qui reste dans le nom d’origine après avoir pris en compte la longueur du prénom (supposé dans la cellule B1) et le deuxième prénom (supposé en C1). (Voir la figure 1.)

image

Figure 1. Séparation des noms en colonnes.

Encore une fois, ces formules fonctionnent si les noms de la colonne A suivent des directives assez strictes. Les noms doivent être au format «premier dernier» ou «premier milieu dernier». S’il y a des qualificatifs dans le nom tels que Mme, Dr, Jr. ou III, vous n’obtiendrez pas de résultats satisfaisants. De plus, si le nom de famille contient un espace (comme dans « John van Kamp » ou « Mary Anne St. Marie ») ou si le nom ne contient aucun espace, vous n’obtiendrez pas non plus de résultats satisfaisants.

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (12383) s’applique à Microsoft Excel 2007, 2010, 2013 et 2016.

Vous pouvez trouver une version de cette astuce pour l’ancienne interface de menu d’Excel ici:

link: / excel-Separating_Names_into_Individual_Columns [Séparation des noms en colonnes individuelles].