Comment extraire un nombre ou un texte à partir d'Excel avec cette fonction

Microsoft Excel est idéal pour travailler à la fois avec des chiffres et du texte Gain de temps avec les opérations de texte dans Excel Gain de temps avec de texte Les opérations dans Excel Excel peut faire de la magie avec des chiffres et gérer les caractères de manière égale. Ce manuel explique comment analyser, convertir, remplacer et modifier du texte dans des feuilles de calcul. Ces bases vous permettront d’effectuer des transformations complexes. En savoir plus, mais si vous utilisez les deux dans la même cellule, vous pourriez rencontrer quelques difficultés. Heureusement, vous pouvez extraire des nombres ou du texte à partir de cellules pour utiliser vos données plus efficacement. Nous présentons plusieurs options, en fonction du format actuel de vos données..

Nombres stockés sous forme de texte

C'est une situation courante et, heureusement, très facile à gérer. Parfois, les cellules ne contenant que des chiffres sont incorrectement étiquetées en tant que texte, ce qui empêche Microsoft Excel de les utiliser dans des opérations. Vous pouvez voir dans l'image ci-dessous que les cellules de la colonne A sont stockées sous forme de texte, comme indiqué par le drapeau vert sur chaque cellule ainsi que la zone de formatage, les deux étant surlignés ci-dessous..

Convertir en nombre

Pour résoudre le problème, il suffit de cliquer dans une cellule, de cocher la case d’avertissement et de sélectionner “Convertir en nombre.” C'est tout ce qu'on peut en dire! Cela peut être fait sur plusieurs cellules en les sélectionnant toutes et en cliquant sur la boîte d'avertissement, mais ce n'est probablement pas aussi efficace que la méthode suivante.

Texte en colonnes

Si de nombreuses cellules doivent être réparées, l’utilisation de cette méthode peut prendre un temps monumental. Pour accélérer le processus, vous pouvez utiliser l'outil Texte en colonnes de Microsoft Excel. Procédure de conversion de fichiers texte délimités en feuilles de calcul Excel. Procédure de conversion de fichiers texte délimités en feuilles de calcul Excel. L'assistant d'importation de texte vous aide à importer des données à partir de texte délimité et à les organiser correctement. Lire la fonction. Sélectionnez les nombres que vous voulez convertir, allez à Données> Texte en colonnes, et utilisez l’assistant pour vous assurer que les chiffres sortent correctement (pour la plupart, il vous suffit de cliquer sur Suivant et terminer sans jouer avec aucun des paramètres).

Le texte sera converti en chiffres et vous êtes prêt à partir. Notez que cela ne fonctionne que sur une colonne à la fois, donc si vous avez beaucoup de colonnes, vous voudrez peut-être utiliser cette méthode finale.

Pâte Spéciale

Pour que cette méthode fonctionne, vous devez entrer le nombre 1 dans une cellule (il est important que ce soit au format numérique). Sélectionnez cette cellule et copiez-la. Maintenant, sélectionnez tous les nombres que vous voulez convertir en format numérique et appuyez sur Édition> Collage spécial. Sélectionner “Multiplier” de la section Opération et cliquez sur OK.

Tous les numéros que vous avez sélectionnés seront convertis au format Général, qui reconnaît les numéros. Toutes les cellules de texte seront également converties au format Général, ce qui ne devrait poser aucun problème..

Extraction de nombres ou de texte à partir de cellules de formats mixtes

Nous arrivons maintenant à la partie la plus difficile: extraire des nombres de cellules contenant plusieurs formats d’entrée. Si vous avez un numéro et une unité (comme “7 pelles,” comme ci-dessous), vous rencontrerez ce problème. Pour résoudre ce problème, nous allons examiner différentes méthodes pour diviser les cellules en chiffres et en texte, afin de vous permettre de les utiliser individuellement. La première méthode est un peu lourde, mais fonctionne très bien sur de petits ensembles de données.

GAUCHE / DROITE & RECHERCHE

La fonction principale que nous allons utiliser ici est LEFT, 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 les un, deux ou trois caractères les plus à gauche des cellules. En combinant GAUCHE avec la fonction RECHERCHE 4 Fonctions de recherche Excel pour une recherche efficace des feuilles de calcul 4 Fonctions de recherche Excel pour une recherche efficace des feuilles de calcul Dans une feuille de calcul Excel géante, CTRL + F vous mènera jusque-là. Soyez intelligent et laissez les formules faire le travail difficile. Les formules de recherche gagnent du temps et sont faciles à appliquer. En savoir plus, nous pouvons tout renvoyer à gauche de l'espace. Voici la fonction:

= LEFT (A1, RECHERCHE ("", A1, 1))

Cela retournera tout à gauche de l'espace. Utilisation de la poignée de remplissage Comment gagner du temps dans Excel en utilisant la poignée de remplissage Comment gagner du temps dans Excel en utilisant la poignée de remplissage Maîtriser la poignée de remplissage est la première étape pour devenir un assistant Excel. Lisez Plus 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 constater, tous les nombres sont maintenant isolés, de sorte qu'ils puissent être manipulés. Voulez-vous également isoler le texte? Nous pouvons utiliser la fonction DROITE de la même manière:

= DROITE (A1, LEN (A1) -SEARCH ("", A1, 1))

Cela renvoie x caractères 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, le texte peut aussi être manipulé. Voulez-vous les combiner à nouveau? Utilisez simplement la fonction CONCATENATE avec toutes les cellules en entrée:

= CONCATENER (E1, F1)

Évidemment, cette méthode fonctionne mieux si vous avez juste des nombres et des unités, et rien d’autre. Si vous avez d'autres formats de cellules, vous devrez peut-être faire preuve de créativité avec des formules pour que tout fonctionne correctement. Si vous avez un jeu de données géant, le calcul de la formule en vaut la peine!

Texte en colonnes

La fonction Texte en colonnes est utile pour les colonnes à chiffres uniquement, mais elle peut également vous faciliter la vie si vous avez des cellules de format mixte. Sélectionnez la colonne avec laquelle vous voulez travailler et appuyez sur Données> Texte en colonnes. Vous pouvez ensuite utiliser l’assistant pour sélectionner un délimiteur (un espace est généralement le meilleur) et faire en sorte que la colonne soit divisée comme vous le souhaitez..

Si vous avez uniquement des nombres à un ou deux chiffres, l'option Largeur fixe peut également être utile, car elle ne divisera que les deux ou trois premiers caractères de la cellule (vous pouvez créer un nombre de divisions si vous le souhaitez, mais je garderai une explication complète de la division en largeur fixe pour un autre article).

Si votre ensemble de données contient de nombreuses colonnes et que vous préférez ne pas utiliser de texte à colonne pour chacune d'elles, vous pouvez facilement obtenir le même effet en effectuant une exportation et une importation rapides. Commencez par exporter votre feuille de calcul sous forme de fichier CSV (valeurs séparées par des virgules). Cliquez sur Fichier> Enregistrer sous… et enregistrez votre fichier au format CSV.

Maintenant, ouvrez une nouvelle feuille de calcul et cliquez sur Fichier> Importer… Sélectionnez votre fichier CSV et utilisez l'assistant d'importation pour diviser vos données en deux colonnes (vous suivrez exactement les mêmes étapes que pour l'assistant de texte en colonnes). En sélectionnant “Espace” Dans la section Délimiteurs, vous indiquez à Microsoft Excel de scinder les données partout où il trouve un espace, ce qui isolera les chiffres et le texte..

Cliquez sur Terminer lorsque vous avez terminé et vous obtiendrez une nouvelle feuille de calcul dans laquelle vos colonnes seront divisées en deux. Bien sûr, si vous avez plus d'un espace dans une cellule, vous aurez plus de deux colonnes, comme vous pouvez le voir ici:

Malheureusement, il n’ya pas de bonne solution pour cela en utilisant cette méthode; vous aurez juste à concaténer les cellules ensemble.

Situations plus compliquées

Avec les stratégies ci-dessus, vous devriez pouvoir extraire des chiffres ou du texte de la plupart des cellules de formats mixtes qui vous posent problème. Même s'ils ne le font pas, vous pouvez probablement les combiner avec certaines des puissantes fonctions de texte incluses dans Microsoft Excel pour obtenir les caractères que vous recherchez. Cependant, il existe des situations beaucoup plus complexes qui appellent des solutions plus complexes.

Par exemple, j'ai trouvé un post sur le 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 donnait la formule suivante comme moyen de le faire:

= SOMMAIRE (MID (0 & A1, GRAND (INDEX (ISNUMBER (- A1, ROW (1 $: 25 $)), 1)) * * ROW (1 $: 25 $), 0), ROW (1 $: 25 $)) + 1, 1) * 10 ^ ROW (1 $: 25 $) / 10)

Pour être tout à fait honnête, je ne sais pas du tout comment cela fonctionne. Mais selon le post du forum, les chiffres seront supprimés d’une chaîne complexe de chiffres et d’autres caractères. Le fait est que, avec suffisamment de temps, de patience et d'effort, vous pouvez extraire des chiffres et du texte à partir de n'importe quoi! Vous devez juste trouver les bonnes ressources Besoin d'aide avec les formules Excel? 7 ressources à consulter Besoin d'aide avec les formules Excel? 7 Ressources à consulter Excel est la norme de référence pour les tableurs. Si vous devez utiliser Excel et que vous devez vous familiariser avec celui-ci, ces ressources vous présenteront rapidement les bases et plus encore. Lire la suite .

Quelle stratégie utilisez-vous??

Maintenant que vous savez mieux comment obtenir des chiffres et des lettres à partir de cellules de formats mixtes, nous voulons savoir quelles autres stratégies vous avez utilisées pour faire la même chose. Publiez vos formules, processus ou autres éléments Excel loufoques qui pourraient être utiles dans les commentaires pour que nous puissions tous en tirer des leçons.!

Explorer plus sur: Microsoft Excel, Spreadsheet.