Comment trier dans Excel des nombres accompagnés d’un préfixe ou d’un suffixe alphabétique ?

Cette méthode consiste à créer une fonction personnalisée en VBA qui extrait uniquement les chiffres des nombres accompagnés d’un préfixe ou d’un suffixe, facilitant ainsi leur tri.

1. Appuyez simultanément sur les touches Alt + F11 pour ouvrir la fenêtre Microsoft Visual Basic pour Applications.

2. Cliquez sur Insertion > Module, puis collez le code VBA ci-dessous dans la nouvelle fenêtre de module qui s’ouvre.

Fonction définie par l’utilisateur : extraire uniquement les chiffres des chaînes de texte

Function num(rng As Range) As String
Dim n As Integer
For n = 1 To Len(rng)
If Mid(rng, n, 1) Like "[0-9]" Then
num = num & Mid(rng, n, 1)
End If
Next n
End Function

3. Enregistrez le code, puis fermez la fenêtre Microsoft Visual Basic pour Applications.

4. Sélectionnez une cellule vide à côté des nombres d’origine comportant un préfixe ou un suffixe, par exemple la cellule B2. Saisissez la formule =num(A2) (où A2 correspond au nombre avec préfixe ou suffixe), puis faites glisser la poignée de recopie sur la plage souhaitée.

5. Étant donné que les chiffres extraits sont stockés sous forme de texte, vous devez les convertir en nombres. Copiez tous les chiffres extraits, cliquez avec le bouton droit sur la plage copiée et sélectionnez Coller les valeurs dans le menu contextuel. Voir la capture d’écran :

Remarque : Dans Excel 2007, copiez les chiffres extraits stockés sous forme de texte, puis cliquez sur Accueil > Collage > Coller les valeurs.

6. Maintenez la sélection sur ces nombres, cliquez sur le bouton à côté des nombres sélectionnés, puis choisissez l’option Convertir en nombredans la liste déroulante. Voir la capture d’écran :

7. Conservez les nouvelles valeurs sélectionnées, puis cliquez sur Données > Trier Du plus petit au plus grand ou Trier Du plus grand au plus petit. Dans la boîte de dialogue d’avertissement qui s’affiche, cochez l’option Étendre la sélection, puis cliquez sur le bouton Trier. Voir captures d’écran :

8. Les nombres accompagnés d’un préfixe ou d’un suffixe alphabétique sont désormais triés comme des nombres classiques (voir la capture d’écran ci-dessous). Supprimez les chiffres extraits si nécessaire.

En réalité, Kutools pour Excel propose également une fonction similaire appelée EXTRACTNUMBERS, qui permet d’extraire les chiffres des chaînes de texte. Ainsi, vous pouvez trier les nombres accompagnés d’un préfixe ou d’un suffixe alphabétique en extrayant d’abord les chiffres.

1. Sélectionnez une cellule vide à côté des nombres comportant un préfixe ou un suffixe alphabétique — par exemple la cellule B2 —, saisissez la formule =EXTRACTNUMBERS(A2;VRAI) (où A2 est la cellule contenant le nombre avec préfixe ou suffixe alphabétique), puis faites glisser la poignée de recopie sur la plage souhaitée. Voir la capture d’écran :

2. Maintenez la sélection sur les chiffres extraits, cliquez sur Données > Trier du plus petit au plus grand ou Trier du plus grand au plus petit ; cochez l’option Étendre la sélection dans la boîte de dialogue d’avertissement de tri qui s’affiche, puis cliquez sur le bouton Trier. Voir les captures d’écran ci-dessous :

3. Les nombres comportant un préfixe ou un suffixe alphabétique sont désormais triés comme des nombres classiques. Vous pouvez supprimer tous les chiffres extraits selon vos besoins.

Kutools pour Excel permet également de séparer le texte et les nombres contenus dans des cellules grâce à son utilitaire Diviser les cellules. Vous pouvez ainsi extraire facilement le texte et les nombres, puis les placer dans des colonnes distinctes pour un tri simplifié. Procédez comme suit :

1. Sélectionnez les nombres comportant un préfixe ou un suffixe alphabétique, puis cliquez sur Kutools > Texte > Diviser les cellules. Voir la capture d’écran ci-dessous :

2. Dans la boîte de dialogue Diviser les cellules qui s’ouvre, cochez l’option Diviser en colonnes dans la section Type, puis sélectionnez Diviser par texte et nombre dans la section Scinder par, et cliquez sur le bouton OK. Voir la capture d’écran ci-dessus :

3. La deuxième boîte de dialogue Diviser les cellules s’affiche maintenant. Sélectionnez la cellule située à côté des nombres d’origine comportant un préfixe ou un suffixe alphabétique — par exemple, la cellule B2 — puis cliquez sur le bouton OK.
 

Les Diviser par texte et nombre ont désormais été scindés et placés à côté des nombres d’origine comportant un préfixe ou un suffixe. Voir capture d’écran ci-dessous.

4. Sélectionnez les chiffres fractionnés, puis cliquez sur Données > Trier du plus petit au plus grand ou Trier du plus grand au plus petit. Cochez ensuite l’option Étendre la sélection dans la boîte de dialogue d’avertissement de tri qui s’affiche, puis cliquez sur le bouton Trier. Voir les captures d’écran ci-dessous :

5. Les nombres accompagnés d’un préfixe ou d’un suffixe alphabétique sont désormais triés comme des nombres classiques. Vous pouvez supprimer les options « Diviser par texte » et « nombre scindé » selon vos besoins.

Cette méthode utilise l’utilitaire Supprimer des caractères spécifiques de Kutools pour Excel afin de supprimer tous les caractères non numériques (y compris les lettres) et de conserver uniquement les chiffres dans les cellules. Vous pourrez ensuite trier facilement les nombres comportant un préfixe ou un suffixe textuel/alphabétique.

1. Copiez tous les nombres dotés d’un préfixe ou d’un suffixe alphabétique, puis collez-les à côté de leurs équivalents d’origine comportant un préfixe ou un suffixe alphabétique.

2. Maintenez la sélection sur les nombres collés, cliquez sur Kutools > Texte > Supprimer des caractères spécifiques, puis cochez uniquement l’option Non numérique dans la boîte de dialogue « Supprimer des caractères spécifiques » qui s’affiche, et cliquez sur le bouton OK. Voir la capture d’écran ci-dessous :

3. Seuls les chiffres restent désormais dans la nouvelle colonne. Maintenez la sélection sur ces chiffres, puis cliquez sur Données > Trier du plus petit au plus grand ou Trier du plus grand au plus petit ; cochez ensuite l’option Étendre la sélection dans la boîte de dialogue d’avertissement de tri qui s’affiche, puis cliquez sur le bouton Trier. Voir les captures d’écran ci-dessous :

4. Supprimez les chiffres selon vos besoins.

Trier Nom complet par Nom de famille dans Excel

Trier rapidement les anniversaires (dates) par mois dans Excel

Trier des cellules par dernier caractère ou chiffre dans Excel

Trier des données par la valeur la plus fréquente dans Excel