Trier par ordre Alphabétique sans tenir compte des nombres

Résolu/Fermé

So62loise Messages postés 8 Date d'inscription mardi 8 juillet 2014 Statut Membre Dernière intervention 9 juillet 2014 - 8 juil. 2014 à 10:59
eriiic Messages postés 24570 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 23 avril 2024 - 10 juil. 2014 à 00:38

Bonjour,

voici mon problème. J'ai une liste de produits chimique que je dois classer par ordre alphabétique. Cependant les produits chimiques contiennent des nombres que je ne dois pas tenir compte. Je m'explique :

1,1-trichloro-2-pentane
4,2-aminobenzene
4,2-amino-2,6-benzol

en chimie ils doivent être classé dans l'ordre suivant :

4,2-aminobenzene
4,2-amino-2,6-benzol
1,1-trichloro-2-pentane

il faut se fixer que sur les lettres et non les chiffres et les espaces ne doivent pas être comptabilisé non plus . Est-ce possible Avec Excel? Il s'agit de Trier environ 5000 noms

Merci Florian

A voir également:

Excel trier par ordre alphabétique en gardant les lignes
Trier avec Excel : toutes les méthodes de tri de données - Guide
Liste déroulante excel - Guide
Aller à la ligne excel - Guide
Mise en forme conditionnelle excel - Guide
Formule excel - Guide

4 réponses

Réponse 1 / 4

eriiic Messages postés 24570 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 23 avril 2024 7 213
8 juil. 2014 à 19:30

Fonction en vba :

Function cle(cellule As String) As String
    Dim i As Long, nbChi As Long
    cle = Replace(Replace(Replace(Replace(cellule, ",", ""), "-", ""), " ", ""), Chr(160), "")
    For i = 1 To Len(cle)
        If IsNumeric(Mid(cle, i, 1)) Then
            cle = Left(cle, i - 1) & Mid(cle, i + 1, 99) & Mid(cle, i, 1)
            nbChi = nbChi + 1
            i = i + 1
        End If
        If i + nbChi = Len(cle) Then Exit For
    Next i
End Function

Plutôt que de supprimer les chiffres je les reporte à la fin.
Ca te permet de trier entre eux les 1-BROM-2-FLUOR-BENZOL et autres 1-BROM-3-FLUOR-BENZOL
S'utilise comme une fonction native d'excel: =cle(A2)

- Alt+F11 pour aller dans vbe
- clic-droit sur le projet (à gauche) et 'insertion / module'
- coller le code
Si tu es sur 2007 ou supérieur enregistrer au format .xlsm
https://www.cjoint.com/c/DGitDjhOX4H

eric

So62loise Messages postés 8 Date d'inscription mardi 8 juillet 2014 Statut Membre Dernière intervention 9 juillet 2014
8 juil. 2014 à 22:48

Eriic merci beaucoup ceci va beaucoup m'aider il y a cependant encore 1 soucis.

Parfois j'ai plus de 2 nombres en amont ou aussi au milieu. Il n'y a que le premier Chiffre qui se met à la fin. Du coup je ne peux pas encore Trier.

Exemple :

1,3,7-TRIMETHYL-XANTHIN => 37TRIMETHYLXANTHIN1
1-3-AMINO-2,7-PROPANOL => 3AMINO7PROPANOL12

J esaye de modifier par moi meme mais je dois tout d'abord comprendre le rôle de chaque fonction

En tout cas merci pour tout.

eriiic Messages postés 24570 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 23 avril 2024 7 213
8 juil. 2014 à 23:21

oui, une petite erreur de signe. J'avançais d'un cran au lieu de reculer lorsque que j'enlevai un chiffre (le caractère suivant recule dans ce cas).
La bonne version :

Function cle(cellule As String) As String
    Dim i As Long, nbChi As Long
    cle = Replace(Replace(Replace(Replace(cellule, ",", ""), "-", ""), " ", ""), Chr(160), "")
    For i = 1 To Len(cle)
        If IsNumeric(Mid(cle, i, 1)) Then
            cle = Left(cle, i - 1) & Mid(cle, i + 1, 99) & Mid(cle, i, 1)
            nbChi = nbChi + 1
            i = i - 1
        End If
        If i + nbChi = Len(cle) Then Exit For
    Next i
End Function

So62loise Messages postés 8 Date d'inscription mardi 8 juillet 2014 Statut Membre Dernière intervention 9 juillet 2014
9 juil. 2014 à 20:38

Bonjour Eriic, merci beaucoup. Cela fonctionne. J'espère de mon coté ne pas avoir oublié une condition mais pour tout ce que j ai souhaité cela marche. Je n ai pas compris toute la saisie VBA mais vais m'y pencher plus en détail ce Weekend, Je clos le Sujet encore Merci

eriiic Messages postés 24570 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 23 avril 2024 7 213
10 juil. 2014 à 00:38

Le principe :
- on élimine les caractères parasites
- pour chaque caractère de la chaine, si c'est un chiffre on prend la partie à gauche, on y ajoute la partie à droite, et on met le chiffre à la fin.

eric

Réponse 2 / 4

via55 Messages postés 14402 Date d'inscription mercredi 16 janvier 2013 Statut Membre Dernière intervention 18 avril 2024 2 702
8 juil. 2014 à 11:09

Bonjour

Si l'initiale sur laquelle doitêtre basé le tri est toujours après le premier - tu peux créer une colonne pour extraire cette initiale
ex avec les noms en A1, en B1 cette formule :
=STXT(B1;CHERCHE("-";B1)+1;1) à étirer ensuite

Et tu fais trier ensuite sur cette colonne

Cdlmnt

So62loise Messages postés 8 Date d'inscription mardi 8 juillet 2014 Statut Membre Dernière intervention 9 juillet 2014
8 juil. 2014 à 11:34

Bonjour via55,
J'ai oublié de préciser que mon Excel est en allemand. Je viens de chercher les formules analogues pour l'allemand. Il me met dans la Colonne B la valeur de 0 partout.

Comme j'ai aussi parfois des noms sans chiffres en tête de cellule peut- etre est- ce pour cela que ca ne marche pas.

cordialement

via55 Messages postés 14402 Date d'inscription mercredi 16 janvier 2013 Statut Membre Dernière intervention 18 avril 2024 2 702
8 juil. 2014 à 11:56

Re

Non il doit te renvoyer soit quelque chose lorsqu'il y a un tiret soit une valeur d'erreur s'il n'en trouve pas .Est tu sûr d'avair bien traduit les formules ?

Pour traiter le cas où il n'y a pas de tiret (qui renvoie une erreur) il faut modifie la formule ainsi :
=SIERREUR(STXT(B10;CHERCHE("-";B10)+1;1);GAUCHE(B10;1))

Je ne peux pas te donner la formule en allemand mais tu vas trouver pour adapter

Cdlmnt

luffydu84 Messages postés 29 Date d'inscription mardi 8 juillet 2014 Statut Membre Dernière intervention 1 septembre 2015 5
8 juil. 2014 à 12:04

si tu es sous windows 8 pour mettre office en français va dans le recherche des programmes et tape office il te proposera des langues sur les autres windows je pense qu 'il faut le trouver autrement

Vaucluse Messages postés 26496 Date d'inscription lundi 23 juillet 2007 Statut Contributeur Dernière intervention 1 avril 2022 6 395
8 juil. 2014 à 12:50

Bonjour tous
c'est assez curieux que le système renvoie 0 s'il y a erreur d'écriture?
ce serait plutôt #REF ou #NOM ou #VALEUR mais pas 0

Je ne voudrais pas mettre les pied ans le plat, mais êtes vous sur que vous n'avez pas de référence circulaire, c'est à dire que la formule ne s'adresse pas à la cellule qui la contient?
et êtes vous sur que vos options excel sont bien en calcul automatique?

crdlmnt

So62loise Messages postés 8 Date d'inscription mardi 8 juillet 2014 Statut Membre Dernière intervention 9 juillet 2014
8 juil. 2014 à 16:55

Il y avait effectivement une reférence circulaire, cette première partie est à présent réglé.Merci beaucoup.
Le Probleme n 'est cependant pas résolu comme je l'ai expliqué dans la réponse suivante. En tout cas merci beaucoup pour vos Conseils et solutions

Réponse 3 / 4

eriiic Messages postés 24570 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 23 avril 2024 7 213
8 juil. 2014 à 13:04

Bonjour à tous,

Proposition qui tient compte que ta chaine peut démarrer par une lettre.
=SI(ESTNUM(--GAUCHE(A2;1));STXT(A2;CHERCHE("-";A2)+1;99);A2)
Dans le fichier tu auras la formule en allemand.
https://www.cjoint.com/c/DGinbOK2zn7
s'il y a encore des erreurs il faudra joindre un fichier avec les lignes en erreur. Tu as peut-être des caractères non annoncés.

eric

So62loise Messages postés 8 Date d'inscription mardi 8 juillet 2014 Statut Membre Dernière intervention 9 juillet 2014
8 juil. 2014 à 16:53

Merci eriiic,

cela fonctionne, j 'ai cependant encore une problématique et c'est quand les noöbre se trouve au milieux du texte:

BROM
1-BROM-2-FLUOR-BENZOL
1-BROM-3-FLUOR-BENZOL
1-BROM-4-FLUOR-BENZOL
1-BROMDODECAN
BROMESSIGSÄURE
BROMESSIGSÄURE-ETHYLESTER
BROMKRESOLPURPUR
4-BROMOBENZYLBROMID
BROMOFORM

Il faut également que je fasse abstraction des chiffrs ,tirets et espaces: l'ordre voulu est le suivant:

BROM
1-BROMDODECAN
BROMESSIGSÄURE
BROMESSIGSÄURE-ETHYLESTER
1-BROM-2-FLUOR-BENZOL
1-BROM-3-FLUOR-BENZOL
1-BROM-4-FLUOR-BENZOL
BROMKRESOLPURPUR
4-BROMOBENZYL-BROMID
BROMOFORM

Cordialement

eriiic Messages postés 24570 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 23 avril 2024 7 213
Modifié par eriiic le 8/07/2014 à 18:37

Je pense qu'il va falloir passer à une fonction ou une procédure en vba.

eric

So62loise Messages postés 8 Date d'inscription mardi 8 juillet 2014 Statut Membre Dernière intervention 9 juillet 2014
8 juil. 2014 à 18:45

Chose que j aurais voulu éviter ....

mais s il n y a pas le choix... enfin vba j 'y ai touché une fois ds ma vie. Ca va donc prendre plus de temps que prévu.

merci tout de même

Réponse 4 / 4

Raymond PENTIER Messages postés 58393 Date d'inscription lundi 13 août 2007 Statut Contributeur Dernière intervention 23 avril 2024 17 096
8 juil. 2014 à 18:57

Je vais proposer une autre approche :
Tu fais un copier-coller de ta colonne A en B.
Dans cette colonne B tu utilises la commande Rechercher-Remplacer pour remplacer les caractères non souhaités par un espace vide "". Cette opération est à exécuter 12 fois : pour la virgule, le tiret et les 10 chiffres. Tu lances le tri sur la colonne B.

Variante 1 : au lieu de faire un copier-coller de A vers B, tu peux mettre en B la formule =SUBSTITUE(SUBSTITUE(A:A;"-";);",";) qui va déjà supprimer les tirets et les virgules ; tu n'auras à exécuter la procédure précédente que 10 fois.

Variante 2 : on peut optimiser la formule en écrivant cette petite "usine à gaz" =SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(A:A;"0";);"1";);"2";);"3";);"4";);"5";);"6";);"7";);"8";);"9";);"-";);",";)
qui marche parfaitement ! Elle n'a qu'un seul défaut, ne pas tourner sur les versions Excel antérieures à 2007, à cause du nombre d'imbrications !
https://www.cjoint.com/c/DGis5dBi8qx

Cordialement.

So62loise Messages postés 8 Date d'inscription mardi 8 juillet 2014 Statut Membre Dernière intervention 9 juillet 2014
9 juil. 2014 à 20:41

Merci Raymond,

mon Excel bien que de 2010 ne voulais pas prendre tous les caractères de la variante 2.

Raymond PENTIER Messages postés 58393 Date d'inscription lundi 13 août 2007 Statut Contributeur Dernière intervention 23 avril 2024 17 096
9 juil. 2014 à 22:53

Fais un copier-coller de ma formule : la moindre erreur de point-virgule, de guillemet ou de parenthèse est fatale ! J'ai travaillé sur Excel 2010 ; donc ça marche ...

Discussions similaires

Afficher toutes les lignes masquées excel

comment mettre en ordre alphabétique

Excel ne filtre pas les derniere donnée ecrite

Discussions similaires

Newsletters