Microsoft Excel est excellent pour travailler avec des nombres et du texte --- mais si vous utilisez les deux dans la même cellule, vous pourriez rencontrer des difficultés. Heureusement, vous pouvez extraire des nombres ou du texte de cellules pour travailler plus efficacement avec vos données. Nous présentons plusieurs options, selon le format dans lequel se trouvent actuellement vos données.
C'est une situation courante, et---heureusement---très facile à gérer. Parfois, les cellules qui ne contiennent que des nombres sont mal étiquetées ou formatées en tant que texte, ce qui empêche Microsoft Excel de les utiliser dans les opérations.
Vous pouvez voir dans l'image ci-dessous que les cellules de la colonne A sont formatées en tant que texte, comme indiqué par la zone de format numérique. Vous pouvez également voir un drapeau vert dans le coin supérieur gauche de chaque cellule.
Si vous voyez le drapeau vert dans le coin supérieur gauche, sélectionnez une ou plusieurs cellules, cliquez sur le signe d'avertissement et sélectionnez Convertir en nombre .
Sinon, sélectionnez les cellules et, dans le menu Format de nombre du ruban, sélectionnez le Numéro par défaut option.
Si vous avez besoin d'options plus précises, cliquez avec le bouton droit sur la ou les cellules en surbrillance et sélectionnez Formater les cellules , qui ouvrira le menu respectif. Ici, vous pouvez personnaliser le format des nombres et ajouter ou supprimer des décimales, ajouter un séparateur de 1 000 ou gérer les nombres négatifs.
Évidemment, vous pouvez également utiliser les options Ruban ou Format de cellule décrites ci-dessus pour convertir un nombre en texte, ou du texte en devise, heure ou tout autre format de votre choix.
Pour que cette méthode fonctionne, vous devez entrer un nombre (n'importe quel nombre) dans une cellule ; il est important que cette cellule soit également formatée comme un nombre. Copiez cette cellule. Maintenant, sélectionnez toutes les cellules que vous souhaitez convertir au format numérique, allez dans Accueil> Coller> Collage spécial , sélectionnez Formats pour coller uniquement la mise en forme de la cellule que vous avez initialement copiée, puis cliquez sur OK .
Cette opération applique le format de la cellule que vous avez copiée à toutes les cellules sélectionnées, même les cellules de texte.
Passons maintenant à la partie la plus difficile :extraire des nombres de cellules contenant plusieurs formats d'entrée. Si vous avez un nombre et une unité (comme "7 pelles", comme nous l'avons ci-dessous), vous rencontrerez ce problème. Pour résoudre ce problème, nous allons examiner différentes manières de diviser les cellules en nombres et en texte, vous permettant de travailler avec chacune individuellement.
Si vous avez beaucoup de cellules qui contiennent un mélange de nombres et de texte ou des multiples des deux, les séparer manuellement peut prendre un temps considérable. Pour accélérer le processus, vous pouvez utiliser la fonction Text to Columns de Microsoft Excel.
Sélectionnez les cellules que vous souhaitez convertir, accédez à Données > Texte en colonnes , et utilisez l'assistant pour vous assurer que les cellules s'affichent correctement. Dans la plupart des cas, vous n'aurez qu'à cliquer sur Suivant et Terminer , mais assurez-vous de choisir un délimiteur correspondant ; dans cet exemple, une virgule.
Si vous n'avez que des nombres à un ou deux chiffres, la largeur fixe L'option peut également être utile, car elle ne séparera que les deux ou trois premiers caractères de la cellule. Vous pouvez même créer un certain nombre de divisions de cette façon.
Remarque : Les cellules au format texte ne seront pas émergent automatiquement avec un formatage numérique (ou vice versa), ce qui signifie que vous devrez peut-être encore convertir ces cellules comme décrit ci-dessus.
Cette méthode est un peu lourde, mais fonctionne très bien sur de petits ensembles de données. Ce que nous supposons ici, c'est qu'un espace sépare le nombre et le texte, bien que la méthode fonctionne également pour tout autre délimiteur.
La fonction principale que nous utiliserons ici est GAUCHE, qui renvoie les caractères les plus à gauche d'une cellule. Comme vous pouvez le voir dans notre ensemble de données ci-dessus, nous avons des cellules avec des nombres à un, deux et trois caractères, nous devrons donc renvoyer le un, deux ou trois caractères les plus à gauche des cellules. En combinant GAUCHE avec la fonction RECHERCHE, nous pouvons tout renvoyer à gauche de l'espace. Voici la fonction :
=GAUCHE(A1, RECHERCHE(" ", A1, 1)) Cela renverra tout à gauche de l'espace. En utilisant la poignée de remplissage pour appliquer la formule au reste des cellules, voici ce que nous obtenons (vous pouvez voir la formule dans la barre de fonctions en haut de l'image) :
Comme vous pouvez le voir, nous avons maintenant tous les chiffres isolés, nous pouvons donc les manipuler. Vous voulez également isoler le texte ? Nous pouvons utiliser la fonction DROITE de la même manière :
=DROITE(A1, LEN(A1)-RECHERCHE(" ", A1, 1)) Cela renvoie X caractères à partir du côté droit de la cellule, où x est la longueur totale de la cellule moins le nombre de caractères à gauche de l'espace.
Maintenant, vous pouvez également manipuler le texte. Vous voulez les combiner à nouveau ? Utilisez simplement la fonction CONCATENER avec toutes les cellules en entrée :
=CONCATENER(E1, F1)
Évidemment, cette méthode fonctionne mieux si vous n'avez que des nombres et des unités, et rien d'autre. Si vous avez d'autres formats de cellule, vous devrez peut-être faire preuve de créativité avec des formules pour que tout fonctionne correctement. Si vous avez un ensemble de données géant, cela vaudra le temps qu'il faut pour comprendre la formule !
Et s'il n'y a pas de délimiteur entre votre numéro et votre texte ?
Si vous extrayez le nombre à gauche ou à droite de la chaîne , vous pouvez utiliser une variante de la formule GAUCHE ou DROITE décrite ci-dessus :
=GAUCHE(A1,SOMME(LEN(A1)-LEN(SUBSTITUE(A1,{"0","1","2","3","4","5","6", "7","8","9"},""))))
=DROITE(A1,SOMME(LEN(A1)-LEN(SUBSTITUE(A1,{"0","1","2","3","4","5","6", "7","8","9"},"")))) Cela renverra tous les nombres à partir de la gauche ou de la droite de la chaîne.
Si vous extrayez le nombre à droite de la chaîne , vous pouvez également utiliser un processus en deux étapes. Tout d'abord, déterminez l'emplacement de votre premier chiffre dans la chaîne à l'aide de la fonction MIN. Ensuite, vous pouvez intégrer ces informations dans une variante de la formule DROITE, pour séparer vos chiffres de vos textes.
=MIN(RECHERCHE({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))
=DROITE(A1, LEN(A1)-B1+1)
Remarque : Lorsque vous utilisez ces formules, n'oubliez pas que vous devrez peut-être ajuster les caractères des colonnes et les numéros de cellule.
Avec les stratégies ci-dessus, vous devriez être en mesure d'extraire des nombres ou du texte de la plupart des cellules au format mixte qui vous posent problème. Même si ce n'est pas le cas, vous pouvez probablement les combiner avec de puissantes fonctions de texte incluses dans Microsoft Excel pour obtenir les caractères que vous recherchez. Cependant, il existe des situations beaucoup plus compliquées qui appellent des solutions plus compliquées.
Par exemple, j'ai trouvé un message de forum où quelqu'un voulait extraire les nombres d'une chaîne comme "45t*&65/", pour qu'il se retrouve avec "4565". Une autre affiche a donné la formule suivante comme moyen de le faire :
=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*LIGNE($1 : 25 $),0),LIGNE($1 :25 $))+1,1)*10^LIGNE($1 :25 $)/10)
Pour être tout à fait honnête, je n'ai aucune idée de comment cela fonctionne. Mais selon le message du forum, cela supprimera les chiffres d'une chaîne compliquée de chiffres et d'autres caractères. Le fait est qu'avec suffisamment de temps, de patience et d'efforts, vous pouvez extraire des chiffres et du texte d'à peu près n'importe quoi ! Il vous suffit de trouver les bonnes ressources.
Après quelques conseils Excel supplémentaires ? Voici comment copier des formules dans Excel.