Extraire et supprimer avec une fonction

[reeves]

Bonjour,
Je viens de trouver ce forum et je pense qu'il va m'être d'une grande aide.
Ca fait plusieurs fois que je travail sur des tableau excel ou je dois extraire certain caractères d'une cellules.
Cette fois ci je n'arrive pas à réaliser ce que je souhaite.
Je vous explique.

J'ai une liste de 35 000 lignes.
Les lignes sont composés en autres d'adresse et je dois m'assurer que chaqu'une des adresses ne dépassent pas 44 caractères. Si l'adresse dépasse, je dois mettre ce qui suit dans une cellule "adresse complèmentaire".

Mais ca ne doit pas couper un mot en plein millieu par exemple voilà un nom (car je dois faire ca aussi avec les nom) "SYNDICAT CONFÉDÉRÉ DES CHIRURGIENS DENTISTES DES HAUTES ALPES" qui a 61 caractères il me semble, je souhaiterai donc que dans une cellule il y est les 44 premiers caractères (et si celà tombe dans un mot alors il prendra la fin du mot d'avant) et dans une autre cellule il doit y avoir le reste.

J'espère que vous allez me comprendre.

Si vous avez besoin d'un peu plus de précision n'hésitez pas.

Reeves

 

[reeves]

Re : Extraire et supprimer avec une fonction

Ou alors si c'est plus simple j'ai ma cellule avec l'adresse, et deux autre vide. Dans une je met les 44 premier et dans la seconde je met ce qui suit.

Ca sera peut etre plus simple mais toujours sans couper un mot en plein milieu

Merci d'avance

 

[Tibo]

Re : Extraire et supprimer avec une fonction

Bonjour,

Avec tes données en colonne A,

en B1, cette formule :

=SI(NBCAR(A1)>44;SI(STXT(A1;45;1)=" ";GAUCHE(A1;44);GAUCHE(A1;TROUVE("µ";
SUBSTITUE(GAUCHE(A1;44);" ";"µ";NBCAR(GAUCHE(A1;44))-NBCAR(SUBSTITUE(
GAUCHE(A1;44);" ";""))))));A1)

en C1, cette formule :

=SI(NBCAR(A1)>44;SI(STXT(A1;45;1)=" ";STXT(A1;46;9^9);1);"")

Formules à recopier vers le bas.

Sur 35000 lignes, ça risque de mettre un certain temps de recalcul...

Je te laisse tester en grand.

@+

 

[reeves]

Re : Extraire et supprimer avec une fonction

Salut et surtout merci pour ta réponse et ton aide.
La première formule marche apparement bien par contre la deuxième m'affiche le chiffre 1 quand il est censé m'afficher quelque chose mais bizarrement celà fonctionne sur quelques cellules enfin sur 2 ou 3 🙂 .

C'est tu d'ou celà vient?

 

[pierrejean]

Re : Extraire et supprimer avec une fonction

bonjour reeves

Salut Tibo 🙂 🙂

un essai en Vba

Sub decompose()
Application.ScreenUpdating = False
tablo = Range("A1:A" & Range("A65536").End(xlUp).Row)
For n = 1 To UBound(tablo)
  x = Split(tablo(n, 1), " ")
  For i = 0 To UBound(x)
      longueur = Len(Range("B" & n) & x(i))
    If longueur < 44 Or bplein = False Then
      Range("B" & n) = Range("B" & n) & x(i) & " "
      bplein = True
    Else
      Range("C" & n) = Range("C" & n) & x(i) & " "
    End If
  Next i
  bplein = False
Next n
Application.ScreenUpdating = True
End Sub

 

[reeves]

Re : Extraire et supprimer avec une fonction

Sinon j'aurai une idée de formule car des fois l'adresse va être Résidence bidule....12 rue machin chose. Et ca c'est attaché donc avec la formule que tu m'as fait des fois il va mal couper. Par exemple. 1er cellule = Résidence machin 12 rue pierre et 2ème cellule = mendès france

Et j'aimerai dans certain cas mettre une formule qui dirait, il extrait de gauche jusqu'a voir un chiffre et la deuxième cellule il extrait tout dès qu'il trouve le chiffre.

Avec ca, je pourrai avoir 1er cellule = Résidence machin 2ème cellules = 12 rue pierre mendès france

Merci d'avance car sinon je sais pas comment je ferai

 

[reeves]

Re : Extraire et supprimer avec une fonction

Merci à toi Pierrejean.
Par contre étant donné que ce language j'y comprend pas grand chose peux tu m'éxpliquer, s'il te plait, ligne par ligne ce que ca fait, afin que je puisse le bidouiller un peu. Car il m'extrait certaine chose mais je vois pas d'où il m'a extrait celà. Pourtant j'ai bien changer le A1 par F2 (puisque ca commence par F2) et B et C par M et N mais ce qui est extrait ne correspond pas à la cellule F qui est à coté.

Enfin je réessaye parceque certain filtre était activé, mais 34000 lignes c'est un peu long. 🙂

 

[pierrejean]

Re : Extraire et supprimer avec une fonction

Re

Voila pour tenir compte de la scission au 1er chiffre

Par contre pour ton fichier :
peux-tu enlever 33955 lignes (lol) et remplacer les autres par des exemples bidons et j'adapterais

 

[reeves]

Re : Extraire et supprimer avec une fonction

Merci pierrejean
Je pense arriver difficilement à mieux. Donc je te remerci ca va vraiment me faire gagner du temps. C'est sur que sur 34000 lignes c'est long même très long au moins 10 minutes mais il m'a bien séparé les adresses.

J'ai plus qu'un passage à faire pour "fignoller" le tout et c'est fini

Encore merci

 

[jp14]

Re : Extraire et supprimer avec une fonction

Bonjour le fil
Bonjour Pierrejean

Ci joint un fichier avec une macro. Le texte est découpé en tranches au dernier espace se trouvant avant la position 44.
Le nombre de caractères n'a aucune importance.

Pour lancer la macro il faut cliquer sur la colonne A

A tester

JP

 

[reeves]

Re : Extraire et supprimer avec une fonction

Euh j'ai une autre petite question, je pense simple pour vous.

Voilà j'ai une première colonne avec des numéros (34000) et j'ai une autre colonne qui possède elle aussi des numéros (à peine 1000), je voudrais que dans une troisième colonne s'inscrive 1 ou 0 ou alors vrai ou faux lorsque il trouve un nombre identique dans les deux colonnes.

Exemple mes premiers nombres de la deuxième colonne sont 24,69,115,128,etc... ma première colonne elle commence de 1 jusqu'a plus de 30000, j'aimerai donc qu'il m'inscrive dans les 23 premières cellules de la troisième colonne faux ou 0 et à la 24ème il m'inscrit vrai ou 1. En gros il scan les deux colonnes et dès qu'il voit un chiffre identique dans les deux colonnes il met vrai ou 1.

Avez vous dans votre manche une petite fonction capable de faire ca?

Je vous remercie d'avance

 

[pierrejean]

Re : Extraire et supprimer avec une fonction

Re

la 1ere colonnne est-elle continue ?
c'est-a-dire numero identiques (ou decalés de meme valeur) au numero de ligne
auquel cas il suffit de savoir si en B il existe un numero < au plus grand de la colonne A
Sinon il faut verifier 1 a 1 tous les numeros de la 2eme colonne pour savoir s'ils existent dans la 1ere ?
peux-tu preciser ?? au besoin avec un fichier exemple (sans données confidentielles)

 

[reeves]

Re : Extraire et supprimer avec une fonction

Euh enfaite j'ai réussi à trouver un cas ressemblant au mien et en bidouillant un peu j'ai reussi à faire ce que je souhaitais.
En tout cas grand merci à pierrejean.

Bon weekend à tous.

Reeves