Fonctions de sous-chaîne Excel : apprenez à les utiliser

Une sous-chaîne fait référence à une chaîne plus petite située dans une chaîne plus grande. Plus précisément, il s’agit d’une chaîne de caractères à gauche, à droite ou au milieu des données. Pour extraire une sous-chaîne Excel, nous pouvons utiliser certaines formules standard basées sur du texte avec une longueur fixe et variable. Nous pouvons également utiliser des techniques non basées sur des formules.

Mais revenons à l’essentiel.

Nous stockons différents types de données dans des cellules dans Excel. Ces données peuvent être un nombre, du texte, une date ou autre chose comme une URL. Les données elles-mêmes sont appelées chaîne. Une sous-chaîne fait partie de la chaîne de données globale.

Fonctions de sous-chaîne Excel

Si vous êtes déjà interrogé sur une fonction de sous-chaîne Excel lors d’un entretien Excel, c’est une question piège ; il n’y en a pas. Mais bien qu’il n’y ait pas de fonctions de sous-chaîne spécifiques dans Excel, il existe toujours des moyens d’extraire une sous-chaîne d’une chaîne de données plus grande.

Si nous devons extraire une sous-chaîne gauche, droite ou médiane d’une chaîne de données plus longue, en particulier lorsqu’il y a des milliers d’enregistrements, nous pouvons utiliser les fonctions LEFT, RIGHT et MID d’Excel. Mais c’est seulement le début. En combinant ces fonctions avec les fonctions LEN et FIND, nous pouvons appliquer presque n’importe quel type d’intelligence et extraire toutes les données dont nous avons besoin. Nous pouvons également utiliser des outils tels que Flash Fill, Find and Replace et Text to Columns.

Commençons par les fonctions de sous-chaîne Excel les plus élémentaires.

GAUCHE

Si vous avez besoin d’extraire un nombre spécifique de caractères du côté gauche d’une chaîne, c’est la fonction qu’il vous faut.

Tout ce que vous devez saisir dans cette formule est le nom de la cellule qui contient la chaîne et le nombre de caractères que vous souhaitez extraire.

= GAUCHE(RÉFÉRENCE, CARACTÈRES)

DROITE

Si vous devez extraire un nombre spécifique de caractères à droite d’une chaîne, la fonction souhaitée est RIGHT. Encore une fois, tout ce que vous devez saisir dans cette formule est le nom de la cellule qui contient la chaîne et le nombre de caractères que vous souhaitez extraire.

= Droite(RÉFÉRENCEPERSONNAGES)

MILIEU

Si vous devez extraire une sous-chaîne du milieu, utilisez la fonction MID dans Excel. Il vous oblige à saisir le nom de la cellule qui contient la chaîne, le numéro de caractère à partir duquel vous avez besoin des données et le nombre de caractères que vous devez extraire.

= Moyen(RÉFÉRENCE, PERSONNAGES)

Avez-vous remarqué que dans chacune des formules ci-dessus, nous avons spécifié un nombre spécifique de chiffres que nous devons extraire ? C’est ce qu’on appelle coder en dur un nombre. Le codage en dur ne peut apporter la bonne réponse que si la chaîne a la même longueur dans chaque ligne, et donc, nous n’avons qu’à extraire le même nombre de caractères dans chaque ligne.

Mais ce n’est pas toujours le cas, et nous aurons généralement besoin d’extraire des caractères dynamiques de la chaîne.

Utilisation de la fonction LEFT avec un nombre de caractères dynamique

La fonction GAUCHE vous demande d’entrer le nombre de caractères à partir de la gauche que vous souhaitez extraire. Cependant, nous devons souvent extraire des données avant un caractère spécifique comme un espace, un point, une virgule ou un signe @.

Pour extraire une sous-chaîne Excel de longueur variable avant un caractère spécifique, nous pouvons combiner la fonction FIND avec la fonction LEFT.

Qu’est-ce que la fonction TROUVER ? Cette fonction trouve un caractère spécifique dans la chaîne et renvoie le numéro auquel se trouve ce caractère.

Ainsi, pour extraire la sous-chaîne de longueur dynamique à gauche de la chaîne, nous devons utiliser la fonction LEFT, et à la place du nombre de caractères, nous devons utiliser la fonction FIND.

= GAUCHE(Cellule, TROUVER(« Caractère », Cellule)-1)

Nous devons faire -1 car nous ne voulons pas introduire ce caractère spécial.

Utilisation de la fonction DROITE avec un nombre de caractères dynamique

Pour calculer la longueur dynamique à partir de la droite, nous devons d’abord calculer la longueur totale à l’aide de la fonction LEN, puis déduire la longueur du texte avant le caractère spécifique (que nous calculons avec la fonction FIND). Cela nous laisse avec un certain nombre de caractères dans la sous-chaîne, qui se trouve à droite du caractère spécial.

• LEN. Renvoie la longueur totale de la chaîne, c’est-à-dire le nombre de caractères dans la chaîne complète.
• TROUVER. Renvoie le nombre de caractères dans la sous-chaîne jusqu’au caractère spécifique.
• LEN – TROUVEZ. Renvoie le nombre de caractères dans la sous-chaîne après le caractère spécifique.

Ainsi, pour extraire une sous-chaîne à l’aide de la fonction RIGHT, nous devons utiliser la fonction RIGHT puis, à la place du nombre de caractères, nous devons utiliser (LEN-FIND).

Trouver le MID entre les caractères spéciaux

Les fonctions MID nous permettent d’entrer le numéro de caractère à partir duquel notre nouvelle sous-chaîne doit commencer et à laquelle elle doit se terminer. Pour choisir les données entre deux caractères spéciaux, nous devons calculer le nombre de caractères des caractères spéciaux. Nous pouvons le faire avec la fonction FIND, bien que ce soit un peu détourné.

Pour compter les caractères jusqu’au premier caractère spécial, utilisez simplement la fonction FIND pour calculer le nombre de caractères. En supposant que le caractère spécial est une parenthèse gauche ou « (« , cela ressemblerait à ceci :

+1 ici s’assure que nous ne ramenons pas le signe (.

Pour compter les caractères jusqu’au deuxième caractère spécial, utilisez la fonction FIND pour compter le nombre de caractères du deuxième caractère. Ici, nous devons déduire le nombre du premier caractère spécial afin que seuls les caractères du milieu soient renvoyés.

En supposant que le deuxième caractère spécial est « ) », et le premier caractère spécial « (« , cela ressemblerait à ceci :

= ((TROUVER(“)”, cellule) – 1 ) – TROUVER(“(“, cellule))

Vous pouvez donc voir que nous pouvons presque extraire n’importe quel type de sous-chaîne en utilisant les fonctions LEFT, RIGHT et MID. Mais ce n’est pas tout! Voici quelques autres solutions de contournement qui vous permettent d’extraire des sous-chaînes ; ils sont un peu longs à utiliser, mais ils sont sans formule et font le travail. Ce sont le « remplissage flash » et le « texte vers les colonnes ».

Extraction d’une sous-chaîne Excel avec du texte dans des colonnes

Si nous devons extraire une sous-chaîne avant un caractère spécial, utilisez l’option « Texte vers les colonnes » dans Excel de manière très simple et sans formule. Discutons-en à l’aide d’un exemple.

Disons que vous devez extraire les noms de l’adresse e-mail. Nous pouvons dire que tout ce qui précède le signe @ est le nom. Ainsi, pour extraire les noms des adresses e-mail, sélectionnez simplement la colonne contenant les adresses e-mail, puis accédez à Barre de formule > onglet Données > Texte en colonnes.

Une fois que vous aurez cliqué dessus, l’écran suivant apparaîtra. Sélectionner Délimité et cliquez sur Suivant.

Si votre caractère spécial est une virgule, un point ou un espace, vous pouvez simplement le choisir en cochant la case. Pour tout autre caractère spécial, vous pouvez cliquer sur ‘autre’ puis insérer le caractère spécial dans la case.

Cliquez maintenant sur Suivant, puis cliquez à nouveau sur Suivant. Cela divisera vos données en deux colonnes, la première colonne contenant les noms et la seconde contenant le reste des données.

Parfois, toutes les données seront importées dans une seule colonne même si elles sont en fait destinées à des colonnes séparées. Cela se produit généralement lorsque vous extrayez des données d’un site Web ou d’un logiciel de bureau.

Dans la capture d’écran ci-dessous, les données qui doivent être réparties sur quatre colonnes ont plutôt été importées dans une colonne.

Une option possible pour extraire des sous-chaînes de ces données consiste à utiliser les fonctions « Texte vers colonne » et « Largeur fixe ». Avec cette option, nous pouvons insérer manuellement des lignes entre les caractères.

Aller à Barre de formule > Données > Texte en colonnes.

Choisissez l’option « Largeur fixe » dans la première étape et cliquez sur Suivant.

Maintenant, cliquez entre le texte pour insérer les lignes. Chaque clic insère une nouvelle ligne, ce qui divise les données en colonnes distinctes. Pour supprimer une ligne, il suffit de double-cliquer sur la ligne.

Cliquez maintenant Suivant et le Finir. Cela vous ramènera à votre classeur Excel avec vos données maintenant divisées en différentes colonnes.

Bien sûr, cela n’est possible que lorsque la longueur de la sous-chaîne est la même dans chaque ligne. Cependant, comme les bases de données logicielles sont conçues de manière très systématique, cette option sera utile dans au moins certains des cas.

Extraire une sous-chaîne avec Flash Fill

Si vous tapez manuellement une sous-chaîne dans la cellule à côté de la chaîne, Excel peut observer le modèle et le reproduire pour vous dans toutes les cellules ci-dessous. Cette fonction s’appelle ‘Flash Fill’.

Continuons avec les mêmes données que nous utilisions dans le dernier exemple. Si nous devons extraire une sous-chaîne commençant par SO avec trois caractères supplémentaires, il nous suffit de taper ces caractères dans la cellule suivante.

Cliquez maintenant sur la cellule B1 et accédez à : Barre de formule > Données > Remplissage Flash.

Clique le Remplissage instantanéet Excel remplira toutes les cellules ci-dessous en utilisant les mêmes critères : caractère 6 à 10.

Essayons encore une fois. Écrivons le type de produit apparaissant vers la fin de la chaîne.

Cliquez maintenant sur le Remplissage instantanéet la magie opère !

Extraire une sous-chaîne avec Rechercher et remplacer

Une autre approche rapide, facile et sans formule pour extraire la sous-chaîne consiste à utiliser la fonction « Rechercher et remplacer » pour remplacer la chaîne indésirable par rien du tout.

Supposons que vous ayez des caractères indésirables dans la chaîne et que vous souhaitiez les supprimer. Sélectionnez la colonne et appuyez sur Ctrl + H sur votre clavier.

Vous pouvez également sélectionner la colonne et accéder à Barre de formule > Accueil > Rechercher et sélectionner > Remplacer.

La fenêtre contextuelle suivante devrait s’ouvrir.

Maintenant, si nous devons supprimer SO du numéro de commande, nous remplirons simplement « SO » dans la case « Rechercher quoi » et laisserons la case « Remplacer par » vide. Cela remplacera SO par rien – c’est-à-dire que cela supprimera simplement SO dans chaque chaîne de la colonne entière.

Utilisation des fonctions de sous-chaîne Excel

L’extraction de sous-chaînes à partir de données dans Excel est assez simple, à la fois avec des formules et sans formules. Tout ce que vous avez à faire est de pratiquer les fonctions LEFT, RIGHT, MID, LEN et FIND. Et si vous ne maîtrisez pas les formules, vous pouvez utiliser les outils Text to Columns, Flash Fill et Find and Replace pour extraire une sous-chaîne d’une chaîne de données.

Maintenant que vous connaissez l’extraction de sous-chaînes en détail, la prochaine étape devrait être d’apprendre à nettoyer les données. Cela commence par des fonctions telles que Excel Trim.

Si vous souhaitez apprendre Microsoft Excel du niveau débutant au niveau avancé avec toutes ces techniques couvrant l’importation de données dans Excel, le nettoyage des données, l’organisation des données selon les meilleures pratiques, l’analyse des données et la visualisation des données, et la création de tableaux de bord significatifs, envisagez un cours Excel pour débutant ou tout autre cours sur Microsoft Excel.