Modification des chaînes de caractères
À partir d’un exemple nous balaierons quelques fonctions texte des tableurs. Il s’agit d’un problème de nettoyage de données, étape incontournable de tout projet d’analyse de données et principal théâtre d’opérations pour les manipulations de texte.
Pour les fonctions d’Excel, nous considérerons la version française (malgré les qualités de la version anglaise qui montre une certaine cohérence avec le SQL). Pour Google Sheets, les noms de fonctions seront en anglais.
Enfin, nous ne traiterons ici que de certaines d'entre elles. Nous vous invitons à lire les pages sur les fonctions texte, les séparations de colonnes et la barre de recherche et remplace en complément de celle-ci.
Données
Notre tâche consistera à nettoyer la table de données saisies manuellement de façon… très peu professionnelle.
Nous supposerons que l’adresse de la cellule « Individu » est A1.
Évidemment, la taille de la table est très petite pour une question de présentation. Il faut imaginer une table de grande dimension.
Nous la nettoierons avec Excel et Google Sheets. Avec ces deux tableurs, vous pouvez la sélectionner entièrement en vous plaçant dedans puis Ctrl A (sous Windows).
Avec Excel, les fonctions sont dans l’onglet Formule. Avec Google Sheets, Insertion puis Fonctions.
Gestion des espaces
Petite précision préalable : en typographie, espace est un nom féminin !
Deux champs de la première colonne ont des espaces superflues. Nous pouvons soit les éliminer directement dans la table, soit créer une nouvelle colonne et y saisir une fonction qui supprime les espaces inutiles. Dans la première option, sélectionnez toute la colonne (ou toute la base) et dans la seconde positionnez-vous sur une troisième colonne (notez qu'avec Excel vous pouvez charger Power Query pour modifier directement les données mais l'opération ne fonctionnera pas avec la chaîne david ricardo qui comporte plusieurs espaces au milieu du texte).
| Remplacer directement | Nouvelle colonne | |
| Excel | = SUPPRESPACE() puis Copier-coller valeurs de la 3ème colonne sur la première | = SUPPRESPACE() (voir page doublons) |
| Google Sheets | Onglet Données puis Nettoyage des données puis Supprimer les espaces vides | = TRIM() |
Finalement, seules les espaces inutiles sont supprimées.
Majuscules et minuscules
Nous souhaitons que les noms propres commencent par des majuscules et que les professions soient écrites en minuscules.
Avec Excel, les fonctions appropriées sont NOMPROPRE() et MINUSCULE(). Avec Google Sheets, PROPER() et LOWER(). Pour obtenir des majuscules, nous aurions utilisé respectivement MAJUSCULE() et UPPER().
Ces formules sont entrées dans les colonnes 3 et 4 puis les valeurs sont recopiées en colonnes 1 et 2 pour écraser les anciennes.
Suppression de caractères
Nous souhaitons à présent supprimer Mr, Mme, etc. Techniquement, nous devons les substituer par… rien.
Une grande table doit être triée ou filtrée pour que toutes ces qualités soient recensées parce nous n’avons d’autre choix que de procéder à autant de substitutions qu’il y a de qualités différentes. Ici il y en a quatre.
Nous créons donc quatre colonnes à droite du tableau. Dans la première, la formule est =SUBSTITUE(A2;"Mr ";""), ce qui règle le cas d’Adam Smith. Dans la seconde nous avons opté pour =SUBSTITUE(C2;"M ";"") pour nous occuper de David Ricardo. Etc.
La dernière est la colonne définitive dont nous copierons les valeurs en première colonne de la table, pour remplacer le joyeux bazar qui s’y trouvait.
Vous remarquez que la fonction SUBSTITUE demande au moins trois informations : la ou les cellules à traiter, le texte à remplacer (entre guillemets et en n’oubliant pas l’espace qui suit les caractères pour ne pas supprimer tous les M qui pourraient se trouver dans les noms !) et ce qu’il faut écrire à la place. Ici ce sont deux guillemets qui se suivent sans rien entre eux ; ainsi le texte à substituer sera tout simplement supprimé. Notez aussi que cette fonction est sensible à la casse (sinon, le m de Adam aurait lui aussi été supprimé).
Avec Google Sheets, on emploie la fonction SUBSTITUTE.
Après suppression des colonnes désormais inutiles, nous en sommes là :
Gauche, droite et décompte de caractères
Des fonctions nous permettent de considérer \(n\) caractères à gauche ou à droite d’une chaîne de caractères.
Nous remarquons que les professions ne sont pas normalisées. En particulier, les intitulés peuvent commencer par un e avec ou sans accent. Nous décidons de remplacer les e sans accent par un é, mais bien sûr seulement lorsqu’il s’agit de la première lettre.
L’opération est un peu plus complexe que les autres car nous devons utiliser une condition : si le premier caractère de gauche est e, remplacer par é ; sinon, aucune action. En langage Excel :
=SI(GAUCHE(B2;1)="e";"é"&DROITE(B2;NBCAR(B2)-1);B2)
Expliquons plus en détail le second argument de la condition. Il s’agit de concaténer notre é avec le reste des caractères de l’intitulé, donc les caractères de droite (il faut les compter avec la fonction NBCAR puisque tous les intitulés de professions n’ont pas le même nombre de caractères) en excluant le premier.
Avec Google Sheets :
=IF(LEFT(B2;1)="e";"é"&RIGHT(B2;LEN(B2)-1);B2)
Normalisation
George Sand est certes une écrivaine mais si nous devons réaliser des statistiques sur ces données, les intitulés des professions doivent être normalisés. La procédure qui suit aurait d’ailleurs pu être employée pour notre étape précédente.
Nous suggérons de réaliser un tableau croisé de façon à dresser la liste des intitulés de profession. Ceci nous permet de savoir quels intitulés doivent être remplacés par quoi.
Ensuite, avec Excel, sélection de la colonne des professions, puis l’onglet Accueil, groupe de commandes Édition, menu Rechercher et sélectionner puis Remplacer… et, après avoir indiqué ce qu’il fallait remplacer et par quoi, clic sur Remplacer tout. Avec Google Sheets, onglet Édition puis Rechercher et remplacer. Cette opération ne nécessite donc pas la création d’une nouvelle colonne avec formules.
