Comment supprimer / diviser le texte et les nombres dans Excel Cell

Ce tutoriel montre plusieurs méthodes de séparation du texte et des nombres dans les cellules Excel, en utilisant à la fois des fonctions intégrées et des fonctions VBA personnalisées. Vous apprendrez à extraire des nombres tout en supprimant le texte, en isolant du texte tout en jetant les nombres et enfin, divisez les données en deux colonnes distinctes.

Disons que vos données ont une colonne où le texte et les nombres sont entremêlés. Pour les données cohérentes, des fonctions simples comme LEFT , RIGHT et MID peuvent suffire. Cependant, les données du monde réel sont souvent incohérentes, les nombres apparaissant avant, après ou dans le texte. Les solutions ci-dessous traitent de cette complexité.

Suppression du texte pour conserver les nombres (Excel 365, 2021, 2019):

Microsoft Excel 2019 a présenté la fonction TEXTJOIN , qui simplifie cette tâche. Les formules ci-dessous exploitent TEXTJOIN , MID , ROW , INDIRECT , SEQUENCE et IFERROR pour y parvenir.

Pour Excel 365 et 2021:

=TEXTJOIN("",TRUE,IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,""))

Pour Excel 365 - 2019:

=TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1,""))

(Dans Excel 2019, cela nécessite Ctrl Shift Entrée pour l'entrée de la formule du tableau. Dans Dynamic Array Excel, une entrée simple suffit.)

Cette formule itère à travers chaque caractère, multipliant par 1 pour convertir les nombres; Les caractères non numériques deviennent des erreurs, manipulées par IFERROR pour laisser des chaînes vides. TEXTJOIN concaténe ensuite les nombres restants.

Fonction VBA personnalisée (toutes les versions Excel):

Pour les versions Excel plus anciennes ou une approche plus simple, une fonction VBA personnalisée offre une solution rationalisée. Deux versions sont fournies:

Code VBA 1 (caractéristique par personnage):

 Fonction RemoveText (Str en chaîne)
  DIM SRES comme chaîne
  sres = ""
  Pour i = 1 à Len (str)
    Si ISNUMERIC (MID (Str, I, 1)) alors SRES = SRES & MID (Str, I, 1)
  Suivant je
  Reposext = sres
Fonction finale

Code VBA 2 (expression régulière):

 Fonction RemoveText (Str en chaîne) en tant que chaîne
  Avec createObject ("vbscript.regexp")
    .Global = true
    .Pattern = "[^ 0-9]"
    ReposExt = .replace (str, "")
  Se terminer avec
Fonction finale

Utilisez ces fonctions avec =RemoveText(A2) . Le code 2 (en utilisant des expressions régulières) fonctionne généralement mieux sur les grands ensembles de données.

Suppression des nombres pour garder le texte (Excel 365, 2021, 2019):

Des formules similaires peuvent extraire du texte tout en supprimant les nombres. Encore une fois, en utilisant TEXTJOIN , MID , ROW , INDIRECT , SEQUENCE , IFERROR et ISERROR :

Pour Excel 365 et 2021:

=TRIM(TEXTJOIN("",TRUE,IF(ISERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1),MID(A2,SEQUENCE(LEN(A2)),1),"")))

Pour Excel 365 - 2019:

=TRIM(TEXTJOIN("",TRUE,IF(ISERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"")))

La fonction TRIM supprime les espaces de tête / arrière.

Fonction VBA personnalisée pour supprimer les numéros (toutes les versions Excel):

Semblable aux fonctions VBA précédentes, voici deux versions pour supprimer les nombres et garder le texte:

Code VBA 1 (caractéristique par personnage):

 Fonction RemovenUmbers (str as String)
  DIM SRES comme chaîne
  sres = ""
  Pour i = 1 à Len (str)
    Sinon ISNUMÉRIQUE (Mid (Str, I, 1)) alors SRES = SRES & MID (Str, I, 1)
  Suivant je
  RemovenUmbers = SRES
Fonction finale

Code VBA 2 (expression régulière):

 Fonction RemovenUmbers (str as string) en tant que chaîne
  Avec createObject ("vbscript.regexp")
    .Global = true
    .Pattern = "[0-9]"
    RemovenUmbers = .replace (str, "")
  Se terminer avec
Fonction finale

Utilisez-les avec =TRIM(RemoveNumbers(A2)) .

Fissure du texte et des nombres en colonnes distinctes:

Une seule fonction VBA personnalisée peut gérer à la fois l'extraction du texte et du numéro:

Code VBA 1 (caractéristique par personnage):

 Fonction SplitTextNumbers (str as string, is_remove_text as booléen) en tant que chaîne
  Dim snum, stext, schar as String
  snum = stext = ""
  Pour i = 1 à Len (str)
    schar = mid (str, i, 1)
    Si ISNUMIERIER (SCHAR) alors SNUM = SNUM & SCHAR ELSE STEXT = STEXT & SCHAR
  Suivant je
  Si is_remove_text alors SplitTextNumbers = Snum else SplitTextNumbers = Stext
Fonction finale

Code VBA 2 (expression régulière):

 Fonction SplitTextNumbers (str as string, is_remove_text as booléen) en tant que chaîne
  Avec createObject ("vbscript.regexp")
    .Global = true
    Si is_remove_text alors .pattern = "[^ 0-9]" else .pattern = "[0-9]"
    SplitTextNumbers = .replace (str, "")
  Se terminer avec
Fonction finale

Use =SplitTextNumbers(A2,TRUE) pour conserver les nombres, et =TRIM(SplitTextNumbers(A2,FALSE)) pour garder le texte.

Utilisation d'un outil spécialisé:

Envisagez d'utiliser un complément Excel dédié pour la suppression des caractères si vous préférez une approche plus visuelle et conviviale. Le tutoriel mentionne un complément "Suite ultime" avec une fonction "Supprimer les caractères".

Ce guide complet fournit plusieurs solutions s'adressant à différentes versions Excel et préférences utilisateur. N'oubliez pas d'ajuster les références cellulaires au besoin pour vos données spécifiques.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!