Filtre et remplacement caracteers

[Aladin]

Bonjour le forum

Je me tourne vers vous car j' ai un filtre complexe à faire, qui se deroulerar suivant plusieurs phase
sur une plage A2-A1600 sur des Adresses IP du style 193.248.1.1-193.248.1.254

1)Prende toutes les cellues avec un 1 avant le tiret et se terminant par 254 et coller en D
2) Sur ce qui a été copié en colonne D
Supprimer tous les éléments apres le tiret
Remplacer tous les chiffres 1 avant le tiret par 0/24
Supprimer le tiret
Je suppose que sur ce coup la l' on ne doit pas travailler sur le combre de caracters car ce sont des plages IP et que la longueur est variable .

Si quelqu' un a une idée je suis preneur pour une automatisation

Merci d' avance de toute aide

Aladin

 

[pierrejean]

Re : Filtre et remplacement caracteers

Bonjour Aladin

Tu sors ta lampe pour nous éclairer au moyen d'un petit fichier exemple comportant quelques lignes typiques et on aura probablement une solution

 

[Modeste]

Re : Filtre et remplacement caracteers

Bonjour Aladin,

Proposer une solution sur base d'un exemple unique serait assez risqué: on ne peut pas deviner quels cas particuliers existent dans ton fichier réel. Y a-t-il toujours un tiret? Et jamais qu'un seul? Le '1' avant le tiret doit-il être "isolé" (donc entre un '.' et le '-') ou un '.241-' est-il à prendre en considération? etc.

D'autre part, remplacer tous les '1' par '0/24' ... y compris le '1' de '193'??

[Edit:] Salut à pierrejean, mon cosmétologue préféré

 

[Aladin]

Re : Filtre et remplacement caracteers

Bonjour à tous
Avec beaucoup de retard j' ai pris connaissance de vos messages et vous envoie en pj ce fichier qui sera peut etre plus parlant
Aladin

 

[Yaloo]

Re : Filtre et remplacement caracteers

Bonjour Aladin, pierrejean ,Modeste ,

Avec cette macro :

Option Explicit
Sub Extraire()
Dim pl, i&, j&
pl = Range("A2:A" & [A65536].End(3).Row)
Columns(4).EntireColumn.ClearContents
j = 1
For i = 1 To UBound(pl)
  If Right(Split(pl(i, 1), "-")(0), 1) = 1 And Right(Split(pl(i, 1), "-")(1), 3) = 254 Then
    Cells(j, 4) = Left(Split(pl(i, 1), "-")(0), Len(Split(pl(i, 1), "-")(0)) - 1) & "0/24"
    j = j + 1
  End If
Next
End Sub

A te relire

Martial

 

[Yaloo]

Re : Filtre et remplacement caracteers

Re,

Sinon avec un tableau 3 fois plus rapide pour 37000 lignes

Sub Extraire2()
Dim pl, i&, j&, t, d()
t = Timer
pl = Range("A2:A" & [A65536].End(3).Row)
Columns(5).EntireColumn.ClearContents
For i = 1 To UBound(pl)
  If Right(Split(pl(i, 1), "-")(0), 1) = 1 And Right(Split(pl(i, 1), "-")(1), 3) = 254 Then
    ReDim Preserve d(j)
    d(j) = Left(Split(pl(i, 1), "-")(0), Len(Split(pl(i, 1), "-")(0)) - 1) & "0/24"
    j = j + 1
  End If
Next
[E1].Resize(UBound(d) + 1) = Application.Transpose(d)
[C3] = Timer - t & " secondes"
End Sub

A+

Martial

 

[Modeste]

Re : Filtre et remplacement caracteers

Bonjour le fil,
Salut Martial

Accuser un certain retard n'a rien de dramatique,
mais prendre un peu de temps en sus
pour répondre à mes questions eût été un plus
à défaut d'avoir un effet magique

Dans un autre registre, une proposition par formule, à coller en B2 ... et recopier vers le bas ... Celle-ci remplace les huns isolés (même s'ils avaient plutôt coutume d'aller en hordes):

=SI(ET(ESTNUM(TROUVE("1-";A2));DROITE(A2;3)="254");GAUCHE(SUBSTITUE(SUBSTITUE(A2;".1.";".0/24.");".1-";".0/24-");TROUVE("-";SUBSTITUE(SUBSTITUE(A2;".1.";".0/24.");".1-";".0/24-"))+-1);"")

... sauf pour les '1' isolés en tout début de chaîne ... mais la précision sera donnée à la prochaine visite d'Aladin

 

[are]

Re : Filtre et remplacement caracteers

Bonjour,

Je débute en macro et pour m'entraîner, je regarde les pbs des utilisateurs.

Pour ce pb, j'avais réuse ce code. Avantage les équivalences entre les lignes sont conservées.

Sub Macro1()
Dim Plage, Plage2 As Range
Dim result As Boolean
 Application.ScreenUpdating = False
ActiveSheet.Range("A2:A" & [A65536].End(3).Row).AutoFilter Field:=1, Criteria1:=Array("=*1-*.254"), Operator:=xlFilterValues
Set Plage = Range("A2:A" & [A65536].End(3).Row).SpecialCells(xlCellTypeVisible)
For Each Cell In Plage
Cell.Copy
Cell.Offset(0, 3).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Cell.Offset(0, 3).Value = Replace(Cell.Offset(0, 3).Value, ".1-", ".0/24-")
    Cell.Offset(0, 3).Value = Left(Cell.Offset(0, 3).Value, InStrRev(Cell.Offset(0, 3).Value, "-") - 1)
    
   Next Cell
End Sub

 

[Victor21]

Re : Filtre et remplacement caracteers

Bonjour, Aladin, pierrejean, Modeste, Yaloo

Par formule, essayez en D2 :
(j'ai compris que le 1 à remplacer est celui présent avant le tiret)

=SI(ET(1*STXT(A2;TROUVE("-";A2)-1;1)=1;1*DROITE(A2;3)=254);GAUCHE(A2;TROUVE("-";A2)-2)&"0/24";"")

edit : bonjour, are

 

[Modeste]

Re : Filtre et remplacement caracteers

Re-bonjour le fil,
Bonjour are,

Je ne suis en aucun cas un spécialiste (pas plus des macros que des formules) et donc mon avis est à prendre pour ce qu'il est: un simple point de vue

Bravo, en tout cas, pour ton initiative: avec les demandes qu'on trouve ici, tu as de quoi t'occuper pendant le reste de ce siècle!

L'idée du filtre me semble intéressante. Pour la copie de la cellule, avant d'en modifier le contenu, ça fonctionnera sans problème sur un volume de données "raisonnable". Sur un volume plus conséquent, tu risques de "perdre du temps" par rapport à la dernière proposition de Yaloo. Mais ce qui est certain, c'est que ça fonctionne! Que demander de plus!?

[Edit:] Salut Patrick

 

[Aladin]

Re : Filtre et remplacement caracteers

Bonjour à tous
merci d' avoir répondu et apporter vos solutions à mon probleme, j' ai tout testé sauf la formule que je testerai plus tard, le résultat est approchant et me donne bien mes addresse 0/24 mais il y a un probleme que faire des adresses de la forme suivanate qui seront inclus dans la liste
10.109.153.1-10.109.153.126
10.109.178.1-10.109.178.2
10.109.184.1-10.109.184.127
Car toutes les adresses ne se termine par 254, il y a des subdivision. il faudrait que la macro recherche bien une fin en 254 pour avant le passage en 0/24, donc dans la liste elle doit ignorer ces adresses
Aladin

 

[Yaloo]

Re : Filtre et remplacement caracteers

Bonjour Aladin, le fil,

Voici ta demande initiale :

Je me tourne vers vous car j' ai un filtre complexe à faire, qui se deroulerar suivant plusieurs phase
sur une plage A2-A1600 sur des Adresses IP du style 193.248.1.1-193.248.1.254

1)Prende toutes les cellues avec un 1 avant le tiret et se terminant par 254 et coller en D
2) Sur ce qui a été copié en colonne D
Supprimer tous les éléments apres le tiret
Remplacer tous les chiffres 1 avant le tiret par 0/24
Supprimer le tiret
Je suppose que sur ce coup la l' on ne doit pas travailler sur le combre de caracters car ce sont des plages IP et que la longueur est variable .

Si quelqu' un a une idée je suis preneur pour une automatisation

Merci d' avance de toute aide

Aladin

En 1), tu nous indiques de prendre les cellules qui finissent par 254 et qui ont un 1 devant le - c'est qui est fait avec les formules et macros ci-dessus, que faire des autres lignes alors là il n'en est pas question dans ton énoncé. S'il faut en faire quelque chose, merci de nous le préciser.

A te relire

Martial

 

[Aladin]

Re : Filtre et remplacement caracteers

Bonsoir àtous
Comme tu l' a resumé Yalou en 1 c' est bien ce que je voidrais que soient modifie ceux qui finisse par 254 et on un 1 devant le tiret.
Pour les autres on ne les touche pas
Aladin

 

[Victor21]

Re : Filtre et remplacement caracteers

Re à tous

=SI(ET(1*STXT(A2;TROUVE("-";A2)-1;1)=1;1*DROITE(A2;3)=254);GAUCHE(A2;TROUVE("-";A2)-2)&"0/24";A2)

 

[Aladin]

Re : Filtre et remplacement caracteers

Bonjour a tous
Merci Patrick, ta formule fonctionne super bien sauf que si il trouve une adresse écrite de cette forme
10.100.232.79-10.100.232.79
10.100.233.4-10.100.233.8
car il attent 3 caracters à la fin il met un #VALEUR dans la cellule d' arrivée, de memem si j' ai déja des adresses sous la forme /24 ou /27 ou /128.
Aladin