Comment diviser nombres et le texte de chaîne dans Excel

Un de nos amis d’Exceltip.com a donc posé cette question dans la section commentaires. «Comment séparer les nombres avant un texte et à la fin du texte en utilisant la formule Excel. Par exemple, 125EvenueStreet et LoveYou3000, etc. » Pour extraire du texte, nous utilisons RIGHT, LEFT, MID et d’autres fonctions de texte. Nous avons juste besoin de connaître le nombre de textes à extraire. Et ici, nous ferons de même en premier.

Extraire le nombre et le texte d’une chaîne lorsque le nombre est en fin de chaîne Pour l’exemple ci-dessus, j’ai préparé cette feuille. Dans la cellule A2, j’ai la chaîne. Dans la cellule B2, je veux la partie texte et dans C2 la partie numérique.

Nous avons donc juste besoin de connaître la position à partir de laquelle le nombre commence. Ensuite, nous utiliserons Left et une autre fonction. Donc, pour obtenir la position du premier nombre, nous utilisons la formule générique ci-dessous:

Cela renverra la position du premier nombre.

Pour l’exemple ci-dessus, écrivez cette formule dans n’importe quelle cellule.

Il renverra 15 car le premier nombre trouvé est en 15ème position dans le texte. Je l’expliquerai plus tard.

Maintenant, pour obtenir du texte, à partir de la gauche, nous avons juste besoin d’obtenir 15-1 caractères à partir de la chaîne. Nous utiliserons donc link: / excel-text-extract-text-from-a-string-in-excel-using-excels-left-and-right-function [fonction LEFT pour extraire le texte.]

Ici, nous avons juste soustrait 1 du nombre retourné par MIN (link: / text-excel-search-function [SEARCH] (\ {0,1,2,3,4,5,6,7,8,9 }, A5 et « 0123456789 »)).

Maintenant, pour obtenir des nombres, nous avons juste besoin d’obtenir des caractères numériques à partir du premier numéro trouvé. Nous calculons donc la longueur totale de la chaîne et soustrayons la position du premier nombre trouvé et y ajoutons 1. Facile. Ouais, c’est juste complexe, c’est simple.

Ici, nous venons d’obtenir la longueur totale de la chaîne en utilisant `link: / len-functio [fonction LEN], puis nous avons soustrait la position du premier nombre trouvé, puis nous y avons ajouté 1. Cela nous donne le nombre total de nombres. En savoir plus ici sur //excel-text/extract-text-from-a-string-in-excel-using-excels-left-and-right-function.html[extrait de texte à l’aide des fonctions GAUCHE et DROITE d’Excel.] `

Ainsi, la partie fonctionnelle GAUCHE et DROITE est simple. La partie Tricky est la partie MIN et SEARCH qui nous donne la position du premier numéro trouvé. Comprenons cela.

Nous savons comment fonctionnent les fonctions GAUCHE et DROITE. Nous allons explorer la partie principale de cette formule qui obtient la position du premier nombre trouvé et qui est:

MIN (lien: / text-excel-search-function [RECHERCHE] (\ {0,1,2,3,4,5,6,7,8,9}, String & « 0123456789 »)

La fonction link: / text-excel-search-function [SEARCH function] retourne la position d’un texte dans une chaîne.

La fonction SEARCH (‘text’, ‘string’) prend deux arguments, d’abord le texte vous voulez rechercher, en second lieu la chaîne dans laquelle vous voulez rechercher.

Ici dans SEARCH, à la position du texte, nous avons un tableau de nombres de 0 à 9. Et à la position de la chaîne, nous avons une chaîne qui est concaténée avec  » 0123456789 « en utilisant l’opérateur & *. Pourquoi? Je vais vous le dire.

Ces nombres sont à la fin donc ne posera aucun problème.

Examinons notre exemple. En A5, nous avons la chaîne contenant le nom de la rue et le numéro de la maison. Nous devons les séparer dans différentes cellules.

Voyons d’abord comment nous avons obtenu notre position du premier nombre dans la chaîne.

MIN (SEARCH (\ {0,1,2,3,4,5,6,7,8,9}, A5 & « 0123456789 »)): cela se traduira par MIN (SEARCH (\ {0,1,2, 3,4,5,6,7,8,9}, «Monta270123456789 *»))

Maintenant, comme je l’ai expliqué, la recherche recherchera chaque nombre dans le tableau \ {0,1,2,3,4,5,6,7,8,9} dans Monta270123456789 et retournera sa position sous forme de tableau. Le tableau renvoyé sera \ {8,9,6,11,12,13,14,7,16,17}. Comment?

0 sera recherché dans la chaîne. Il se trouve en position 8. Par conséquent, notre premier élément est 8. Notez que notre texte original ne comporte que 7 caractères. Tu piges. 0 ne fait pas partie de Monta27. Le 1 suivant sera recherché dans la chaîne et il ne fait pas non plus partie de la chaîne d’origine, et nous obtenons sa position 9.

Les 2 suivants seront recherchés. Puisqu’il s’agit de la partie de la chaîne d’origine, nous obtenons son index comme 6.

De même, chaque élément se trouve à une certaine position.

Et l’histoire qui suit est assez simple. Nous utilisons ce numéro d’extrait de texte et de nombres en utilisant les fonctions GAUCHE et DROITE.

Extraire le nombre et le texte d’une chaîne lorsque Number est au début de la chaîne Dans l’exemple ci-dessus, Number était à la fin de la chaîne. Comment extraire le nombre et le texte lorsque le nombre est au début.

J’ai préparé un tableau similaire à celui ci-dessus. Il a juste un numéro au début.

Ici, nous utiliserons une technique différente. Nous allons compter la longueur des nombres (qui est de 2 ici) et extraire ce nombre de caractères à gauche de String.

Donc la méthode est = LEFT (chaîne, nombre de nombres)

Pour compter le nombre de caractères, c’est la formule.

Ici, ** link: / excel-text-formulas-excel-substitute-function [SUBSTITUTE function] remplacera chaque nombre trouvé par “” (vide). Si un nombre est trouvé ti substitué et qu’une nouvelle chaîne sera ajoutée au tableau, une autre chaîne d’origine sera ajoutée au tableau. De cette façon, nous aurons un tableau de 10 chaînes.

Maintenant, la fonction LEN retournera la longueur des caractères dans un tableau de ces chaînes.

Ensuite, de la longueur des chaînes d’origine, nous soustrayons la longueur de chaque chaîne retournée par la fonction SUBSTITUTE. Cela renverra à nouveau un tableau.

Maintenant, SUM ajoutera tous ces nombres. C’est le nombre de nombres dans la chaîne.

Maintenant que nous connaissons la longueur des nombres en chaîne, nous substituerons cette fonction à GAUCHE.

Puisque nous avons notre chaîne un A11 notre:

Puisque nous connaissons le nombre de nombres, nous pouvons le soustraire de la longueur totale de la chaîne pour obtenir les alphabets numériques dans la chaîne, puis utiliser la fonction de droite pour extraire ce nombre de caractères à droite de la chaîne.

La partie principale des deux formules est SUM (LEN (A11) -LEN (SUBSTITUTE (A11, \ {« 0 », « 1 », « 2 », « 3 », « 4 », « 5 », « 6 », « 7 », « 8 », « 9 »}, «  »)))

qui calcule la première occurrence d’un nombre. Ce n’est qu’après avoir trouvé cela que nous sommes en mesure de diviser le texte et le nombre en utilisant la fonction GAUCHE. Alors, comprenons cela.

Cette partie renvoie un tableau de chaînes dans A11 après avoir remplacé ces nombres par rien / vide («»). Pour 27Monta, il retournera \ {« 27Monta », « 27Monta », « 7Monta », « 27Monta », « 27Monta », « 27Monta », « 27Monta », « 2Monta », « 27Monta », « 27Monta »}.

LEN (SUBSTITUT (A11, \ {« 0 », « 1 », « 2 », « 3 », « 4 », « 5 », « 6 », « 7 », « 8 », « 9 »},  » « )):

Maintenant, la partie SUBSTITUTE est enveloppée par la fonction LEN. Cette longueur de retour des textes dans le tableau retourné par la fonction SUBSTITUTE. En conséquence, nous aurons \ {7,7,6,7,7,7,7,6,7,7}.

LEN (A11) -LEN (SUBSTITUT (A11, \ {« 0 », « 1 », « 2 », « 3 », « 4 », « 5 », « 6 », « 7 », « 8 »,  » 9 « }, » « )):

Ici, nous soustrayons chaque nombre renvoyé par la partie ci-dessus de la longueur de la chaîne réelle. La longueur du texte original est de 7. Nous aurons donc \ {7-7,7-7,7-6, …​.}. Enfin, nous aurons \ {0,0,1,0,0,0,0,1,0,0}.

SUM (LEN (A11) -LEN (SUBSTITUTE (A11, \ {« 0 », « 1 », « 2 », « 3 », « 4 », « 5 », « 6 », « 7 », « 8 » , « 9 »}, «  »))):

Ici, nous avons utilisé SUM pour additionner le tableau retourné par la partie ci-dessus de la fonction.

Cela donnera 2. Quel est le nombre de nombres dans la chaîne.

Maintenant, en utilisant cela, nous pouvons extraire les textes et les numéros et les diviser dans différentes cellules. Cette méthode fonctionnera avec les deux types de texte, lorsque le nombre est au début et à la fin. Il vous suffit d’utiliser les fonctions GAUCHE et DROITE de manière appropriée.

Les méthodes ci-dessus sont un peu complexes et ne sont pas utiles lorsque le texte et les nombres sont mélangés. Pour diviser le texte et les nombres, utilisez cette fonction définie par l’utilisateur.

String: la chaîne que vous souhaitez fractionner.

Op: c’est booléen. Passez 0 ou false pour obtenir une partie de texte. Pour la partie numérique, passez vrai ou tout nombre supérieur à 0.

Par exemple, si la chaîne est dans A20 alors,

Et

Copiez le code ci-dessous dans le module VBA pour que la formule ci-dessus fonctionne.

Ce code vérifie simplement chaque caractère de la chaîne, si c’est un nombre ou non. S’il s’agit d’un nombre, il est stocké dans la variable num, sinon dans la variable txt. Si l’utilisateur passe true pour op alors num est retourné sinon txt est retourné.

C’est le meilleur moyen de séparer le nombre et le texte d’une chaîne à mon avis.

Vous pouvez télécharger le classeur ici si vous le souhaitez.

Alors oui les gars, ce sont les moyens de diviser le texte et les nombres dans différentes cellules. Faites-moi savoir si vous avez des doutes ou une meilleure solution dans la section commentaires ci-dessous. C’est toujours amusant d’interagir avec les gars.

`link: /wp-content-uploads-2019-11-Split-Number-and-Text-from-A-Cell.xls [__ Split Number and Text from A Cell]

link: / mail-send-and-receive-in-vba-how-to-extract-domain-name-from-email-in-excel [Comment extraire un nom de domaine d’email dans Excel]

lien: / excel-text-formulas-split-numbers-and-text-from-string-in-excel [Split Numbers and Text from String in Excel]

lien: / clavier-formule-raccourcis-50-excel-raccourcis-pour-augmenter-votre-productivité [50 raccourcis Excel pour augmenter votre productivité]

link: / formulas-and-functions-introduction-of-vlookup-function [La fonction RECHERCHEV dans Excel]

lien: / tips-countif-in-microsoft-excel [COUNTIF dans Excel 2016]

link: / excel-formule-et-fonction-excel-sumif-function [Comment utiliser la fonction SUMIF dans Excel]