Comment extraire les chiffres d’une cellule dans Excel

Extraire uniquement les chiffres d’une cellule contenant du texte et des nombres mélangés est une tâche courante dans le traitement des données. Voici plusieurs méthodes pour y parvenir.

1. Utiliser une formule avec une colonne auxiliaire

Excel ne fournit pas de fonction native spécifique pour extraire uniquement les chiffres, mais vous pouvez combiner plusieurs fonctions pour atteindre cet objectif.

Formule avec des fonctions Excel :

1. Si vos données se trouvent dans la colonne A (exemple : cellule A1), utilisez cette formule dans une colonne vide (par exemple, B1) : Formule simple : =CONCATENER(SI(ESTNUM(DROITE(A1;1));DROITE(A1;1);"")) Formule avancée avec tableau matriciel : =CONCATENER(SI(ESTNUM(STXT(A1;LIGNE(INDIRECT("1:"&NBCAR(A1)));1));STXT(A1;LIGNE(INDIRECT("1:"&NBCAR(A1)));1);""))
2. Appuyez sur Ctrl + Shift + Entrée après avoir saisi la formule pour qu’elle fonctionne comme une formule matricielle.
3. Étirez la formule vers le bas pour appliquer à toutes les lignes.

Explication :

• STXT : Extrait un caractère spécifique dans la chaîne.
• ESTNUM : Vérifie si le caractère est un nombre.
• NBCAR : Compte le nombre total de caractères.
• CONCATENER : Assemble les chiffres extraits en une seule chaîne.

2. Utiliser une macro VBA pour extraire les chiffres

Si vous travaillez avec de grandes quantités de données ou si vous avez besoin d’une solution flexible, une macro VBA est idéale.

Code VBA :

Function ExtraireChiffres(Texte As String) As String
    Dim i As Integer
    Dim Resultat As String
    Resultat = ""
    
    For i = 1 To Len(Texte)
        If Mid(Texte, i, 1) Like "[0-9]" Then
            Resultat = Resultat & Mid(Texte, i, 1)
        End If
    Next i
    
    ExtraireChiffres = Resultat
End Function

Étapes :

1. Appuyez sur Alt + F11 pour ouvrir l’éditeur VBA.
2. Cliquez sur Insertion > Module.
3. Collez le code ci-dessus.
4. Fermez l’éditeur et retournez dans Excel.
5. Dans une cellule vide, utilisez la fonction personnalisée comme suit : =ExtraireChiffres(A1)

Exemple :

• Si la cellule A1 contient abc123de4, la formule renverra 1234.

3. Utiliser Power Query pour extraire les chiffres

Power Query est idéal pour le nettoyage de données complexes.

Étapes :

1. Sélectionnez vos données, allez dans Données > Obtenir et transformer les données > Depuis une table/plage.
2. Dans l’éditeur Power Query :
   • Ajoutez une colonne personnalisée : Text.Select([Colonne], {"0".."9"})
   • Cela extrait uniquement les chiffres de la chaîne.
3. Cliquez sur Fermer et charger pour importer les données nettoyées dans Excel.

4. Utiliser un script Python (facultatif)

Si vous travaillez avec des scripts externes ou des outils intégrés comme Python (avec Excel), vous pouvez utiliser une expression régulière pour extraire les chiffres.

Exemple de code Python :

import pandas as pd

# Exemple de données
data = {"Colonne": ["abc123", "456xyz", "78&90"]}
df = pd.DataFrame(data)

# Extraire les chiffres
df["Chiffres"] = df["Colonne"].str.extract("(\d+)", expand=False)
print(df)

5. Utiliser des fonctions combinées pour des cas spécifiques

Exemple : Extraire les nombres séparés

Si les chiffres sont séparés dans la cellule, utilisez TEXTE et EXTRAIRE pour isoler les blocs numériques.

Formule pour extraire un nombre à une position précise :

=SI(ESTNUM(STXT(A1;1;1));STXT(A1;1;1);"")

Cas pratiques

1. Nettoyer des identifiants :
   • Cellule : ABC1234DEF
   • Résultat : 1234.
2. Extraire des montants dans des cellules mélangées :
   • Cellule : $123.45ABC
   • Résultat : 12345.
3. Analyser des données téléphoniques :
   • Cellule : (123) 456-7890
   • Résultat : 1234567890.

Conseils

• Vérifiez vos données sources : Assurez-vous que les cellules contiennent des caractères valides.
• Combinez plusieurs méthodes : Par exemple, utilisez Power Query pour des données volumineuses et des formules Excel pour des opérations rapides.
• Testez les macros VBA sur des copies de vos données pour éviter les erreurs.

Ces méthodes vous permettront d’extraire efficacement les chiffres de vos cellules Excel selon vos besoins.

Suite : Cas pratiques et Méthodes Avancées pour Extraire les Chiffres dans Excel

6. Combiner l’extraction avec d’autres transformations

Dans des cas où vos données nécessitent à la fois une extraction et une transformation (comme reformater les données après extraction), vous pouvez imbriquer les méthodes précédentes avec d’autres fonctions Excel ou Power Query.

Cas pratiques avancés

Cas 1 : Extraire et formater des numéros de téléphone

Les numéros de téléphone peuvent contenir des espaces, des parenthèses ou des tirets. Voici comment extraire les chiffres et reformater le résultat.

Données source :

(123) 456-7890

Formule Excel :

=CONCATENER("(",STXT(A1;1;3),") ",STXT(A1;4;3),"-",STXT(A1;7;4))

VBA pour automatisation :

Function ExtraireNumTel(Texte As String) As String
    Dim i As Integer
    Dim Chiffres As String
    Dim FormatTel As String
    Chiffres = ""

    ' Extraire uniquement les chiffres
    For i = 1 To Len(Texte)
        If Mid(Texte, i, 1) Like "[0-9]" Then
            Chiffres = Chiffres & Mid(Texte, i, 1)
        End If
    Next i

    ' Ajouter le format standard (XXX) XXX-XXXX
    If Len(Chiffres) = 10 Then
        FormatTel = "(" & Left(Chiffres, 3) & ") " & Mid(Chiffres, 4, 3) & "-" & Right(Chiffres, 4)
    Else
        FormatTel = Chiffres ' Si le format est incorrect, afficher les chiffres
    End If

    ExtraireNumTel = FormatTel
End Function

Utilisez la fonction dans une cellule :

=ExtraireNumTel(A1)

Résultat formaté :

(123) 456-7890

Cas 2 : Extraire plusieurs blocs numériques

Si vos données contiennent plusieurs blocs de chiffres séparés, utilisez Power Query pour extraire chaque bloc séparément.

Données source :

Code: 123, ID: 456, Value: 789

Étapes dans Power Query :

1. Chargez les données dans Power Query.
2. Séparez les colonnes par délimiteur (ex. :, , ou espace).
3. Utilisez Text.Select pour extraire uniquement les chiffres dans chaque colonne.
4. Combinez les résultats dans une seule colonne ou gardez-les séparés.

Résultat :

• Bloc 1 : 123
• Bloc 2 : 456
• Bloc 3 : 789

7. Gérer des cas spécifiques avec Power Query ou VBA

Extraire uniquement les chiffres après un mot-clé spécifique

Données source :

Order1234Status5678

VBA Exemple :

Function ExtraireAprèsMotClé(Texte As String, MotClé As String) As String
    Dim Position As Integer
    Dim i As Integer
    Dim Resultat As String
    Resultat = ""

    ' Trouver la position du mot-clé
    Position = InStr(1, Texte, MotClé, vbTextCompare)
    If Position > 0 Then
        ' Extraire les chiffres après le mot-clé
        For i = Position + Len(MotClé) To Len(Texte)
            If Mid(Texte, i, 1) Like "[0-9]" Then
                Resultat = Resultat & Mid(Texte, i, 1)
            Else
                Exit For ' Arrêter si un caractère non numérique est rencontré
            End If
        Next i
    End If

    ExtraireAprèsMotClé = Resultat
End Function

Utilisez cette fonction dans Excel pour extraire les chiffres après un mot-clé spécifique :

=ExtraireAprèsMotClé(A1, "Order")

Résultat :

1234

8. Nettoyage et vérification des résultats

Nettoyage avec SUPPRESPACE

Après avoir extrait les chiffres, supprimez les espaces inutiles :

=SUPPRESPACE(A1)

Validation avec des règles

Pour vérifier que seules des valeurs numériques sont extraites, utilisez une mise en forme conditionnelle :

1. Sélectionnez la plage des résultats.
2. Allez dans Mise en forme conditionnelle > Nouvelle règle.
3. Utilisez la formule suivante : =ESTNUM(A1)
4. Appliquez une mise en surbrillance pour identifier les cellules non valides.

9. Astuces pour optimiser l’extraction

1. Travaillez par lots : Si vos données sont volumineuses, utilisez Power Query pour des performances optimales.
2. Utilisez des colonnes auxiliaires : Cela facilite la révision et le nettoyage des données.
3. Testez vos solutions : Essayez d’abord sur un échantillon pour éviter des erreurs sur l’ensemble des données.

Résumé des méthodes : Choisissez selon vos besoins

Autres articles

Série d’exercices corrigés - Excel Perfectionnement

Voici une série d’exercices conçus pour perfectionner vos compétences Excel....

Read more

Guide : Calculer une moyenne avec un...

Excel offre plusieurs méthodes pour calculer une moyenne tout en...

Read more

Utiliser les fonctions pour afficher la date...

Excel propose plusieurs fonctions pour insérer ou manipuler la date...

Read more