Manipuler du texte dans Excel : nettoyer, extraire et assembler

Tu récupères un export, une liste de contacts, un fichier client, et c'est le chaos : des « JEAN dupont » à côté de « marie Curie », des espaces qui traînent, un nom et un prénom collés dans la même cellule. Avant de pouvoir trier, filtrer ou rechercher quoi que ce soit, il faut remettre ce texte en ordre, et les fonctions de texte font ce travail automatiquement, sur des milliers de lignes, sans retoucher une seule cellule à la main.

La bonne nouvelle, c'est que tu n'as pas besoin de retenir vingt fonctions par cœur, parce que tout besoin sur du texte se résume à trois gestes que tu vas vite avoir dans les doigts.
Nettoyer, d'abord, c'est enlever ce qui dérange, comme les espaces qui traînent ou une casse écrite n'importe comment. Extraire, ensuite, c'est prendre un morceau précis, le prénom seul ou le domaine d'une adresse. Assembler, enfin, c'est recoller plusieurs bouts en un seul, par exemple un nom et un prénom réunis dans une même cellule.
Une fois que tu ranges chaque problème dans l'une de ces trois cases, choisir la bonne fonction devient presque évident, et c'est tout l'objet de cette leçon.

Le conseil du pro

Avant tout traitement de texte, nettoie d'abord avec SUPPRESPACE. Un espace invisible en fin de cellule fait échouer la moitié des recherches et des comparaisons, et c'est la cause numéro un des « ça ne marche pas » sur les données importées.

Assembler : coller plusieurs textes

C'est sans doute le besoin que tu rencontreras le plus souvent, parce qu'on passe son temps à réunir un nom et un prénom, à fabriquer un identifiant ou à construire une phrase à partir de plusieurs cellules.
Deux outils suffisent à couvrir tous ces cas, et le bon choix dépend simplement du nombre de morceaux que tu as à recoller.

L'esperluette & pour quelques morceauxC'est la plus directe, puisque tu colles les bouts les uns derrière les autres avec le signe &, et que tout texte fixe comme un espace ou une virgule se glisse entre guillemets.

Avec le prénom en B2 et le nom en A2, cette formule renvoie « Jean Dupont » avec un espace au milieu. Recopie-la vers le bas et toute la liste se reconstruit ligne après ligne.

CONCAT pour une plage entièreDès que tu as beaucoup de cellules à recoller, elle prend le relais et t'évite de répéter les & un par un, ce qui rend la formule bien plus lisible.

Cette formule colle d'un coup tout le contenu de A1 à E1, tandis que sa cousine TEXTE.JOINDRE va plus loin en ajoutant un séparateur automatique entre chaque élément.

Le saviez-vous ?

CONCAT est arrivée avec Excel 2016 pour remplacer l'ancienne fonction CONCATENER, plus longue à écrire. CONCATENER fonctionne toujours par souci de compatibilité, mais CONCAT accepte en plus une plage entière comme A1:E1, là où l'ancienne obligeait à lister chaque cellule une par une.

Extraire : prendre un morceau de texte

Ici, c'est le mouvement inverse qui t'intéresse, puisqu'il s'agit d'isoler une partie d'une chaîne plutôt que d'en recoller plusieurs.
Tu en auras besoin dès que tu voudras récupérer un code postal au milieu d'une adresse, le domaine d'une adresse e-mail ou les initiales d'un nom, et trois fonctions se partagent ce travail selon l'endroit où se trouve le morceau cherché.

GAUCHE et DROITE pour les bordsGAUCHE prend les premiers caractères d'une chaîne, donc =GAUCHE(A1;3) ramène les 3 premiers, ce qui rend service pour un préfixe, un code ou un indicatif.
DROITE fait l'exact opposé en prenant les derniers, si bien que =DROITE(A1;4) garde les 4 derniers caractères, par exemple une année, une extension ou un suffixe.

STXT pour piocher au milieuQuand le morceau n'est ni au début ni à la fin, STXT devient la plus souple des trois, parce que tu lui donnes la position de départ puis le nombre de caractères à prendre.
Ainsi =STXT(A1;3;5) part du 3e caractère et en récupère 5, exactement comme si tu posais le doigt à un endroit de la chaîne avant de compter.

Nettoyer : espaces et casse

Quand tes données sont propres, elles se trient, se comparent et se recherchent sans jamais te surprendre, alors que des données sales font échouer la moitié de tes formules de façon incompréhensible.
Deux familles de nettoyage reviennent tout le temps, et tu vas constamment retomber dessus, ce sont les espaces parasites d'un côté et la casse écrite n'importe comment de l'autre.

SUPPRESPACE fait le ménage des espacesLa formule =SUPPRESPACE(A1) retire les espaces de début et de fin, et réduit les espaces doubles à l'intérieur à un seul, tout en laissant intacts les espaces simples entre les mots.
C'est le geste réflexe sur n'importe quel import de données, et il t'épargne une bonne partie des bugs invisibles.

Trois fonctions pour régler la casseNOMPROPRE, via =NOMPROPRE(A1), met une majuscule au début de chaque mot, ce qui est parfait pour remettre des noms en ordre, puisque « jean DUPONT » devient « Jean Dupont ».
Quand tu veux tout en capitales, MAJUSCULE s'en charge, et MINUSCULE fait l'inverse en basculant tout en bas de casse.

Vrai ou faux ?

« SUPPRESPACE enlève tous les espaces d'une cellule. » Faux : elle ne retire que les espaces de début et de fin, et réduit les espaces doubles du milieu à un seul. Les espaces simples entre les mots restent bien en place, sinon « Jean Dupont » se transformerait en « JeanDupont ».

Chercher et remplacer dans le texte

Voilà deux besoins qui vont souvent de pair, parce qu'avant de remplacer quelque chose, tu as d'abord besoin de savoir où ce quelque chose se trouve dans la chaîne.
D'un côté tu repères la position d'un caractère, de l'autre tu échanges un texte contre un autre sur toute une colonne d'un seul coup.

CHERCHE et TROUVE pour la positionLa formule =CHERCHE("@";A1) te renvoie la position du « @ » dans une adresse, et c'est précisément ce qui te sert à repérer un séparateur juste avant une extraction.
La seule nuance entre les deux tient à la casse, puisque CHERCHE l'ignore tandis que TROUVE la respecte, donc tu choisis l'une ou l'autre selon que la majuscule compte ou non.

SUBSTITUE pour remplacerLa formule =SUBSTITUE(A1;"-";" ") change chaque tiret en espace partout dans la cellule.
Là où elle bat le Rechercher-Remplacer du menu , c'est qu'elle se recopie sur toute la colonne et se remet à jour toute seule dès que les données changent, sans que tu aies à relancer quoi que ce soit.

Exemple travaillé : séparer « Nom Prénom » en deux colonnes

Prenons le cas que tu croiseras tôt ou tard, celui d'une colonne A qui contient « Dupont Jean » alors que tu voudrais le nom d'un côté et le prénom de l'autre.
C'est l'occasion parfaite de voir plusieurs fonctions de texte travailler ensemble sur un vrai problème, plutôt qu'une par une dans le vide.

Le texte brut est en colonne A. L'objectif est de remplir les colonnes B et C automatiquement en repérant l'espace qui sépare le nom du prénom, puis en découpant de part et d'autre.

Comment séparer « Nom Prénom » en deux colonnes en 3 étapes

1. 1

   Repère l'espace qui sépare les deux. =CHERCHE(" ";A2) renvoie la position de l'espace, par exemple 7 pour « Dupont Jean », et ce chiffre sert de point de découpe à tout le reste.

2. 2

   Extrais le nom, avant l'espace. =GAUCHE(A2;CHERCHE(" ";A2)-1) prend tous les caractères jusqu'à l'espace, moins un pour ne pas l'inclure, ce qui donne « Dupont ».

3. 3

   Extrais le prénom, après l'espace. =DROITE(A2;NBCAR(A2)-CHERCHE(" ";A2)) récupère tout le reste, à savoir la longueur totale donnée par NBCAR moins la position de l'espace, et il reste « Jean ».

Le combo gagnant

Tu viens de combiner CHERCHE, GAUCHE, DROITE et NBCAR en trois étapes, et c'est exactement comme ça que les fonctions de texte prennent toute leur puissance, en s'emboîtant les unes dans les autres. Sur les versions récentes d'Excel, FRACTIONNER.TEXTE fait tout ça en une seule formule, mais le réflexe de fond ne change pas : tu repères un séparateur, puis tu découpes de part et d'autre.

Avec ces quelques fonctions, tu as déjà de quoi remettre d'aplomb la plupart des fichiers texte qui te tomberont entre les mains, et tu peux piocher au besoin dans la catégorie Texte du dictionnaire pour le détail de chaque fonction.
Reste une donnée qui résiste encore plus que le texte aux débutants, ce sont les dates, parce qu'Excel ne les stocke pas du tout comme on les lit. C'est tout le sujet de la prochaine leçon, gérer les dates dans Excel, où tu apprends à calculer des écarts, à décaler des mois et à compter les jours ouvrés sans te tromper.