Découper un texte sur les espaces avec Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Fragmenter le contenu d'une cellule

Découper le texte d'une cellule sur les espaces ou sur chaque caractère est la solution que nous apportons dans ce cas pratique. Le résultat à obtenir peut-être intéressant à plus d'un titre. Pour un moteur de recherche, il est essentiel d'analyser chaque mot clé à recouper avant d'interroger la base de données. Pour nettoyer une expression des caractères non souhaités, chaque occurrence doit être traitée indépendant. Ces techniques peuvent aussi être exploitées pour encrypter des messages. Et nous utiliserons ces travaux dans une prochaine formation pour le développement d'une telle application.



La solution finalisée est illustrée par la capture ci-dessus. Un texte est tapé dans une zone de saisie. Le découpage est réalisé sur chaque mot de la phrase dans une première rangée. Un autre découpage est entrepris dans une seconde rangée pour séparer chaque lettre dans des cellules indépendantes.

Source et présentation de la problématique
Ces travaux peuvent être réalisés à partir d'un classeur vierge. Nous proposons néanmoins de réceptionner une structure existante.

• Télécharger le classeur decouper-sur-lettres-et-espaces.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous découvrons la feuille dotée de la zone de saisie pour recevoir le texte à tronçonner. Ensuite, deux sections vierges sont aptes à recevoir nos formules.



La première est destinée à découper la phrase sur chaque mot. A ce titre, vous notez que cette section est composée de deux rangées. La première doit servir à repérer la position de tous les espaces dans le texte saisi. C'est en fonction de ces positions que nous livrerons les mots indépendamment dans la seconde rangée.

La deuxième section n'est constituée que d'une seule rangée. En effet, l'extraction de chaque lettre d'un texte est une opération beaucoup triviale.

Isoler chaque lettre d'un texte
Comme la technique pour découper une phrase sur chaque caractère est plus simple, nous proposons de débuter par ces travaux. Comme vous le savez, la fonction Excel STXT permet de prélever une portion d'une chaîne de caractères :

=Stxt(Texte_à_découper; Position_de_départ; Longueur_de_découpe)

En premier argument, nous devons lui indiquer la cellule du texte saisi par l'utilisateur. En deuxième argument, nous devons préciser à partir de quelle position le prélèvement doit être effectué. Il s'agit d'ailleurs du seul paramètre variable dans ce cas. Cette position doit suivre le déplacement de la formule répliquée vers la droite. Nous exploiterons donc la fonction Excel Colonne pour pointer sur chaque nouvelle lettre. Cette fonction retourne en effet l'indice de colonne de la cellule passée en paramètre. Le dernier argument est fixe. Nous souhaitons extraire chaque lettre indépendamment. La découpe doit donc immuablement être réalisée sur une longueur d'un caractère.

• Sélectionner la case de la première lettre à extraire, soit la cellule B11,
• Taper le symbole égal (=) pour initier la formule,
• Taper la fonction de découpe suivie d'une parenthèse, soit : Stxt(,
• Désigner le texte à fragmenter en cliquant sur la cellule B4,
• Enfoncer la touche F4 du clavier pour la figer, ce qui donne : $B$4,

En effet, pour extraire indépendamment chaque lettre du terme saisi, nous devons répliquer cette formule sur les colonnes de droite. Mais chaque calcul ainsi déplacé devra toujours faire référence à ce texte inscrit dans la cellule B4 qui ne doit donc pas bouger.

• Taper un point-virgule (;) pour passer dans l'argument de la position de départ,
• Saisir la fonction suivante suivie d'une parenthèse : Colonne(,
• Désigner une cellule de la première colonne de la feuille, par exemple A1,

Son indice de colonne vaut 1. Il indique donc la position de départ pour la première extraction. En répliquant la formule sur la droite, A1 deviendra A2 puis A3 et ainsi de suite. L'indice évoluera automatiquement pour prélever la lettre suivante à partir de la nouvelle position incrémentée.

• Fermer la parenthèse de la fonction Colonne,
• Taper un point-virgule (;) pour passer dans l'argument de la longueur de découpe,
• Saisir le chiffre 1 pour ne garder qu'un seul caractère au fil de la progression,
• Fermer la parenthèse de la fonction Stxt,
• Valider la formule par le raccourci CTRL + Entrée pour garder la cellule active,
• Tirer la poignée du résultat sur la droite jusqu'en colonne L,

Comme vous pouvez le voir, toutes les premières lettres de la phrase sont effectivement extraites et rangées indépendamment dans des cellules. Bien sûr, la zone d'extraction n'est pas prévue suffisamment longue pour restituer tous les caractères. Seule la solution nous intéresse ici.



La syntaxe complète de la formule que nous avons construite est la suivante :

=STXT($B$4;COLONNE(A1);1)

Positions des espaces dans un texte
Désormais, avant de pouvoir réaliser la découpe de la phrase sur les mots qu'elle contient, nous devons repérer la position des espaces. La fonction Stxt pourra alors fragmenter l'expression en réalisant la découpe entre la position d'un espace et du suivant. Pour déceler ces positions, nous devons exploiter la fonction Excel Cherche :

=Cherche(Texte_cherché; Texte_de_recherche; Position_de_départ)

En premier argument, nous devons lui indiquer le texte cherché. Il s'agit de l'espace à mentionner entre guillemets. En deuxième argument, nous devons lui stipuler dans quel texte chercher. Il s'agit de la cellule B4 accueillant la saisie de l'utilisateur. Enfin, en dernier argument, nous devons lui fournir la position à partir de laquelle effectuer cette recherche. Cette position doit en effet évoluer en même temps que la formule est répliquée. Elle doit à chaque fois repartir du dernier espace trouvé.

• Sélectionner la première position à trouver en cliquant sur la cellule B7,
• Taper le symbole égal (=) pour initier la formule,
• Taper la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Cliquer sur la cellule à gauche du calcul, soit la cellule A7,
• Puis, taper le critère suivant : ='',

En effet, dans le processus de réplication, nous avons besoin de savoir si un précédent espace a déjà été trouvé. Si tel n'est pas le cas, donc si la cellule précédente est vide, nous savons que la recherche doit être engagée à partir du premier caractère de la chaîne.

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Saisir la fonction de recherche suivie d'une parenthèse, soit : Cherche(,
• Taper l'espace à chercher entre guillemets, soit : ' ',
• Taper un point-virgule (;) pour passer dans l'argument de la chaîne de recherche,
• Désigner la saisie de l'utilisateur en cliquant sur la cellule B4,
• Enfoncer la touche F4 du clavier pour la figer dans le calcul, ce qui donne : $B$4,

En effet, pour les mêmes raisons que celles que nous avons évoquées précédemment, cette cellule ne doit pas suivre le déplacement qui sera imposé par la réplication de la formule.

• Taper un point-virgule (;) pour passer dans l'argument de la position de départ,
• Saisir le chiffre 1 pour indiquer un prélèvement depuis le début de la chaîne,
• Fermer la parenthèse de la fonction Cherche,
• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,

Désormais, pour fournir des indications explicites au calcul qui suivra pour la découpe des mots, nous devons nous assurer qu'un prochain espace existe avant de tenter de le trouver. En effet, en cas d'échec, la fonction Cherche renvoie un message d'erreur compromettant la bonne tenue de l'extraction qui doit suivre. C'est pourquoi, nous avons besoin de réaliser un nouveau test dans l'enchaînement.

• Saisir de nouveau la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Saisir la fonction pour énumérer les critères, suivie d'une parenthèse, soit : Et(,
• Taper la fonction de test numérique, suivie d'une parenthèse, soit : EstNum(,
• Inscrire la fonction de recherche suivie d'une parenthèse, soit : Cherche(,
• Désigner l'espace à chercher entre guillemets, soit : ' ',
• Taper un point-virgule (;) pour passer dans l'argument de la chaîne de recherche,
• Désigner la saisie utilisateur en cliquant sur sa cellule B4,
• Enfoncer la touche F4 du clavier pour la figer, ce qui donne : $B$4,
• Taper un point-virgule (;) pour passer dans l'argument de la position de départ,
• Cliquer de nouveau la cellule A7 et lui ajouter une unité, soit : A7+1,

Ainsi, pour tester la présence potentielle d'un prochain espace, nous partons de la potentielle précédente position trouvée. Si la fonction EstNum retourne Vrai, donc si le critère est satisfait, nous saurons qu'il existe.

• Fermer la parenthèse de la fonction Cherche,
• Puis, fermer la parenthèse de la fonction EstNum,
• Taper un point-virgule (;) pour poursuivre l'énumération des critères,
• Saisir alors la condition suivante : A7>0,

En effet, lorsque plus aucun espace ne sera trouvé, nous souhaitons le mentionner ensuite par le chiffre 0 dans la case. Ce critère consiste donc à ne pas tenter de reprendre la recherche lorsque cette valeur est inscrite.

• Fermer la parenthèse de la fonction Et,
• Taper un point-virgule (;) pour passer dans la branche Alors de la seconde fonction Si,
• Copier l'expression de recherche du critère précédent, soit : Cherche('';$B$4;A7+1),

Lorsqu'un prochain espace existe bien et que la précédente cellule n'est pas sanctionnée par la valeur 0, nous retournons la position de cet espace.

• Taper un point-virgule (;) pour passer dans la branche Sinon de la seconde fonction Si,
• Saisir le chiffre zéro (0) pour indiquer que le bout de chaîne est atteint,
• Fermer la parenthèse de la seconde fonction Si,
• Puis, fermer la parenthèse de la première fonction Si qui l'englobe,
• Valider la formule par le raccourci CTRL + Entrée pour garder la cellule du résultat active,
• Tirer la poignée de cette dernière sur la droite jusqu'en colonne L,

Toutes les positions apparaissent instantanément. Les espaces sont désormais référencés par leurs emplacements. Vous notez qu'à l'issue de la phrase, le chiffre 0 vient parfaitement clore les tentatives.



La syntaxe complète de la formule de repérage que nous avons bâtie est la suivante :

=SI(A7=''; CHERCHE(' '; $B$4; 1); SI(ET(ESTNUM(CHERCHE(''; $B$4;A7+1)); A7>0);CHERCHE(' '; $B$4; A7+1); 0))

Extraire les mots d'un texte
Nous allons maintenant pouvoir exploiter ces positions référencées pour découper la chaîne sur les espaces afin d'extraire chacun des mots indépendamment. La technique consiste à exploiter de nouveau la fonction de découpe Stxt. Mais il faut différencier trois cas. Il s'agit du contexte du premier espace, en partant de la première position. Il s'agit du cas où deux espaces existent, un avant et un après. Et enfin, il s'agit du cas où le chiffre 0 sanctionne la fin de l'énumération. Donc, nous devons imbriquer les fonctions Si pour honorer ces contraintes. Et nous allons englober la syntaxe dans la fonction de gestion d'erreur SiErreur.

• Sélectionner la case du premier mot à trouver, soit la cellule B8,
• Taper le symbole égal (=) pour initier la formule,
• Saisir la fonction de gestion d'erreur suivie d'une parenthèse, soit : SiErreur(,
• Saisir la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Cliquer sur la cellule A8 à gauche du calcul,
• Puis, taper l'égalité suivante : ='',

Si ce critère est vérifié, nous savons qu'aucun mot précédent n'a été extrait. En conséquence, il s'agit du premier à trouver. Donc, le prélèvement doit débuter à partir du premier caractère de la chaîne.

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Saisir la fonction de découpe suivie d'une parenthèse, soit : Stxt(,
• Cliquer sur la cellule B4 pour désigner le texte à découper,
• Enfoncer la touche F4 du clavier pour la figer lors de la réplication de la formule,
• Taper un point-virgule (;) pour passer dans l'argument de la position de départ,
• Saisir le chiffre 1 pour débuter le traitement par le début de la chaîne,
• Taper un point-virgule (;) pour passer dans l'argument de la longueur à prélever,
• Sélectionner la cellule donnant la position du premier espace trouvé, soit : B7,

Ainsi, nous demandons de prélever la chaîne en partant du premier caractère jusqu'au premier espace. Il en résultera le premier mot.

• Fermer la parenthèse de la fonction Stxt,
• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Saisir de nouveau la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Puis, taper le critère suivant : B7=0,

Souvenez-vous, tel que nous avons conçu le calcul sur la recherche des positions, cette valeur indique que plus aucun espace n'existe. Donc, il s'agit de prélever l'information restante jusqu'à la fin de la chaîne. Comme nous ne connaissons pas sa longueur, nous allons indiquer une valeur volontairement grande. La fonction Stxt se chargera d'ajuster le prélèvement.

• Taper un point-virgule (;) pour passer dans la branche Alors de cette seconde fonction Si,
• Saisir la fonction de découpe suivie d'une parenthèse, soit : Stxt(,
• Désigner le texte à découper en cliquant sur sa cellule B4,
• Enfoncer la touche F4 du clavier pour la figer, ce qui donne : $B$4,
• Taper un point-virgule (;) pour passer dans l'argument de la position de départ,
• Désigner la précédente position trouvée en cliquant sur la cellule A7,
• Taper un point-virgule (;) pour passer dans l'argument de la longueur à prélever,
• Puis, saisir le nombre 1000 pour prévoir très large,
• Fermer la parenthèse de la fonction Stxt,
• Taper un point-virgule (;) pour passer dans la branche Sinon de cette seconde fonction Si,

Ce dernier cas correspond à celui des mots placés au milieu de la chaîne. Il existe donc un espace avant et un espace après. Le prélèvement doit débuter depuis la position précédente trouvée. Et il doit se réaliser sur une longueur équivalente à la différence entre la position de l'espace avant et de l'espace après. Cette opération donne en effet le nombre de caractères compris dans l'intervalle, soit le mot à isoler.

• Saisir de nouveau la fonction de découpe suivie d'une parenthèse, soit : Stxt(,
• Désigner le texte à découper en cliquant sur sa cellule B4,
• Enfoncer la touche F4 pour la figer dans le calcul, ce qui donne : $B$4,
• Taper un point-virgule (;) pour passer dans l'argument de la position de départ,
• Sélectionner la précédente position trouvée en cliquant sur la cellule A7,
• Taper un point-virgule (;) pour passer dans l'argument de la longueur à prélever,
• Cliquer sur la cellule B7 de la position du prochain espace,
• Taper le symbole moins (-) pour engager la soustraction,
• Cliquer sur la cellule A7 de la position du précédent espace,
• Fermer la parenthèse de la fonction Stxt,
• Fermer la parenthèse de la seconde fonction Si,
• Fermer la parenthèse de la première fonction Si qui l'englobe,
• Taper un point-virgule (;) pour passer dans l'argument de gestion d'erreur,
• Saisir deux guillemets ('') pour garder la cellule vide lorsque la fin de chaîne est atteinte,
• Fermer la parenthèse de la fonction SiErreur,
• Valider la formule par le raccourci CTRL + Entrée afin de garder le résultat actif,
• Cliquer et glisser la poignée de la cellule sur la droite jusqu'en colonne L,

Comme vous pouvez le voir, tous les mots sont parfaitement extraits et isolés dans leurs cellules respectives et ce, jusqu'au dernier. Si vous changez la phrase en B4, les extractions s'actualisent parfaitement.