Isoler une partie de texte d'une cellule avec position variable ?

[DukeDevlin]

Bonjour,

Je viens vers vous aujourd'hui pour avoir vos lumières. En effet, vous trouverez en PJ un tableau expliquant mon problème. J'ai une cellule contenant un descriptif assez long. Et j'aimerais isoler 2 références à partir de ce descriptif : Le NCA et le NCR. Je sais que le NCA a 4 chiffres et le NCR 5 chiffres et que l'un comme l'autre peuvent être placés qu'au début (à gauche) et non pas à la fin. Le problème, c'est que la nomenclature utilisée peut changer d'une cellule à une autre comme joint dans l'exemple, sinon cela serait trop facile et il suffirait d'utiliser la fonction GAUCHE. Avez-vous une idée pour que je clean le fichier en faisant ressortir les NCA et NCR pour chaque descriptif dans deux nouvelles colonnes ? Je pense que cela sera plus parlant avec l'exemple.

Merci à vous. Bonne journée.

 

[patricktoulon]

re
oui absolument j'avais saisie le principe c'est le"*" qui me trompait (je l'avais pas vu)


je l'ai adapté dans une simple boucle for sans diminution de chaine
on garde même la ponctuation d'origine et on jump autant de fois le "n" qu'avec ton do/loop

Function les_nombres2(cel As String, nombre) As String
'adaptation dans une simple boucle for de la methode jumping "n" avec pattern absolu de jmfmarques
    Dim i&, n&, x$, pattern$
    'traduction:"[autre char que numerique]" & chaine numerique de n nombres fixe  & "[autre char que numerique]et le reste"
    pattern = "[!0-9]" & String(nombre, "#") & "[!0-9]*"
    cel = " " & cel  'add first space
    n = 1
    For i = n To Len(cel)
        If Mid(cel, n) Like pattern Then
            'x = x & " " & Mid(cel, n, nombre + 1): n = n + nombre + 1 'avec ponctuation
            x = x & " " & Mid(cel, n + 1, nombre): n = n + nombre  'sans ponctuation
        Else: n = n + 1
        End If
    Next
    les_nombres2 = x
End Function

sans ponctuation / avec ponctuation

 

[DukeDevlin]

Merci franchement vous êtes tops avec toutes ces solutions. Je sens que je vais me perdre demain, je testerai ça et je suis sûr que j’aurais de nouveau de quoi vous embêter vu que le fichier n’est pas clean du tout. Mais oui 3 constantes : L’un a 4 chiffres, l’autre 5 et c’est forcément en début de cellule. Après l’un comme l’autre peut avoir un seul code ou deux. Encore merciii !

 

[micheldu52]

Bonjour à tous,
Voici une base que je viens d'écrire...
Si cela semble convenir dis moi ce qu'il faut changer...
Cordialement,
Michel

 

[DukeDevlin]

Hello,

J'essaie de tester les solutions au plus vite. Saturation des VPN au boulot, Excel n'apprécie pas trop ça, et difficile de travailler correctement sur nos fichiers. Merci !

 

[DukeDevlin]

Hello,

Fausse alerte, il y a certaines cellules qui ont le chiffre à la fin MAIS quand c'est le cas, cela semble bien précisé avant "NCR" ou "NCA" de ce que je vois.

Question annexe : Pour faire un brouillon de mon fichier de travail qui présente déjà des macros, j'ai C/C en valeurs mon tableau dans un autre Excel. Pourtant, impossible de travailler, dès que je fais un "Save" ou une manoeuvre ça met 3 plombes à se charger et j'ai pourtant fait Formules -> Option de calcul -> Manuel. Mais cela met toujours autant de temps, comment être sûr qu'il n'y a pas des formules/calculs qui se font en fond ? Pour que je teste enfin vos codes. Merci.

 

[DukeDevlin]

UPDATE:

J'ai regardé mes 4000 lignes, il y a "seulement" 400 valeurs uniques avec plusieurs cas de figure :

1) La cellule contient que du texte.
2) La cellule ne contient aucun nombre à 4 ou 5 chiffres.
3) La cellule contient un seul nombre à 4 chiffres.
4) La cellule contient plusieurs nombres à 4 chiffres.
5) La cellule contient un seul nombre à 5 chiffres.
6) La cellule contient plusieurs nombres à 5 chiffres.
7) La cellule contient un nombre à 4 chiffres et un nombre à 5 chiffres.
8) La cellule contient plusieurs nombres à 4 chiffres et un nombre à 5 chiffres.
9) La cellule contient plusieurs nombres à 4 chiffres et plusieurs nombres à 5 chiffres.

Ces derniers peuvent être séparés par un espace, un ".", un "/" ou par la dénomination NCA et NCR. Ces nombres se trouvent souvent au début, parfois au milieu et d'autres fois à la fin (Milieu = 1234 blabla 1235 78900 blabla).

Le but ? Extraire dans une colonne TOUS les nombres à 4 chiffres d'une cellule et dans une autre colonne TOUS les nombres à 5 chiffres de cette même cellule. Si plusieurs chiffres, idéalement, faire l'extraction avec un séparateur type "/".

Je ne sais pas si je suis clair. Du coup, en sachant ça, quelle formule fonctionne dans chacun de ces cas pour que je fasse le teste ? Merci !

 

[mapomme]

Bonsoir @DukeDevlin,

Donc du texte "C'est vraiment super 3456 ", on extrait bien le 3436 alors qu'au départ on le laissait tomber?

Donc la position des nombres importe peu. C'est soit un nombre à 4 ou 5 chiffres et dans ce cas on le retient sinon on l'élimine.

 

[patricktoulon]

re
Bonjour

Je ne sais pas si je suis clair. Du coup, en sachant ça, quelle formule fonctionne dans chacun de ces cas pour que je fasse le teste ? Merci !

ben oui et non car on le prenais au début , après il fallait plus le prendre , maintenant il faut le reprendre


a titre indicatif dans ton classeur demo en post 1 dans la partie je souhaiterais tu ne le prends pas
faut savoir ou on a mal

 

[DukeDevlin]

On m’avait dit que c’était qu’au début et j’ai regardé le fichier excel pas clean du tout et 5% des données sont à la fin et d’autres au milieu. Donc je revois le cahier des charges malheureusement. Je m’en excuse. Donc oui l’idée c’est de prendre tous les nombres de 4 chiffres et 5 chiffres pour l’autre. Est-ce possible ?

 

[mapomme]

Bonsoir,

Donc oui l’idée c’est de prendre tous les nombres de 4 chiffres et 5 chiffres pour l’autre. Est-ce possible ?

Si plusieurs chiffres, idéalement, faire l'extraction avec un séparateur type "/".

Voir le fichier joint avec un code réadapté pour la fonction NcaNcr.

 

[patricktoulon]

re
celle ci adaptée de celle de jmfmarques prend tout les numéros valides
et garde le caractère séparateur d'origine de la chaîne

Function les_nombres2(cel As String, nombre) As String
'adaptation dans une simple boucle for de la méthode jumping "n" avec pattern absolu de jmfmarques
    Dim i&, n&, x$, pattern$
    'traduction:"[autre char que numerique]" & chaine numerique de n nombres fixe  & "[autre char que numerique]et le reste"
    pattern = "[!0-9]" & String(nombre, "#") & "[!0-9]*"
    cel = " " & cel & " " 'add first space
    n = 1
    For i = n To Len(cel)
        If Mid(cel, n) Like pattern Then
            x = x & " " & Mid(cel, n, nombre + 1): n = n + nombre + 1 'avec ponctuation
            'x = x & " " & Mid(cel, n + 1, nombre): n = n + nombre  'sans ponctuation
        Else: n = n + 1
        End If
    Next
    les_nombres2 = x
End Function

 

[laurent950]

Bonsoir Patrick et le forum,
Fonction : =res(B4)
Condition pour NCA et NCR
If Cells(3, ActiveCell.Column) = "NCA" Then / Ligne 3 de la cellule active ActiveCell.Column

Avec se pattern cela peux être bon aussi ? .Pattern = "\d{4,5}"

Function Res(ByVal Mot As Range) As String
Dim Reg As Object
Dim Tabres() As Variant
Dim cpt As Byte: cpt = 1
Set Reg = CreateObject("vbscript.regexp")
With Reg
    .Pattern = "\d{4,5}"
    .MultiLine = True: .IgnoreCase = True: .Global = True:  'MsgBox .test(Mot.Value)
    If .test(Mot.Value) = False Then Exit Function
        If .test(Mot.Value) = True Then
            Set Matches = .Execute(Mot.Value)
            ReDim Tabres(1 To Matches.Count, 1 To 2)
                For Each Match In Matches
                        Tabres(cpt, 1) = Match.Value
                        Tabres(cpt, 2) = Match.Length
                        cpt = cpt + 1
                Next Match
        End If
End With
    For i = LBound(Tabres, 1) To UBound(Tabres, 1)
        If Tabres(i, 2) = 4 Then
            If Chaine4 = Empty Then Chaine4 = Tabres(i, 1) Else Chaine4 = Chaine4 & " / " & Tabres(i, 1)
        ElseIf Tabres(i, 2) = 5 Then
            If Chaine5 = Empty Then Chaine5 = Tabres(i, 1) Else Chaine5 = Chaine5 & " / " & Tabres(i, 1)
        End If
    Next i
If Cells(3, ActiveCell.Column) = "NCA" Then
    Res = Chaine4
ElseIf Cells(3, ActiveCell.Column) = "NCR" Then
    Res = Chaine5
End If
End Function

 

[patricktoulon]

puré l'usine ça doit en bouffer du pétrole ça
le raisonnement de @jmfmarques est la meilleures solution selon moi après on la décantera sous diverses méthodes mais le principe est le même
un caractère non numérique x caractères numérique demandé et un caractères non numérique
que l'on teste avec un espace supplementaire de chaque coté de la chaine
avec like ca donne
pattern = "[!0-9]" & String(nombre, "#") & "[!0-9]*" boucle sur len ou le split

pour le regex
.pattern = "(\D{1})(\d{" & x & "})(\D{1})x?" le regex coupe tout seul
et le voila ton regex

Function les_4_ou_5_faut_savoir(var As String, x As Long) As String
    Dim Reg As Object, matches As Object, i&, txt$
    Set Reg = CreateObject("vbscript.regexp")
    With Reg
       .pattern = "(\D{1})(\d{" & x & "})(\D{1})x?"
        .MultiLine = True: .IgnoreCase = True: .Global = True:    'MsgBox .test(Mot.Value)
        Set matches = .Execute(" " & var & " ")
        If matches.Count > 0 Then
           ' MsgBox var & vbCrLf & matches.Count & vbCrLf & matches(0)
            For i = 0 To matches.Count - 1: txt = txt & matches(i): Next
        End If
    les_4_ou_5_faut_savoir = txt

    End With
End Function

pas la peine d'en faire des patacaisses

 

[laurent950]

J'ai simplifier la version ResBis
=ResBis(B4)

Function ResBis(ByVal Mot As Range) As String
Dim Reg As Object
Set Reg = CreateObject("vbscript.regexp")
With Reg
    .Pattern = "\d{4,5}"
    .MultiLine = True: .IgnoreCase = True: .Global = True:  'MsgBox .test(Mot.Value)
    If .test(Mot.Value) = False Then Exit Function
        If .test(Mot.Value) = True Then
            Set Matches = .Execute(Mot.Value)
                For Each Match In Matches
                    If Match.Length = 4 Then
                        If Chaine4 = Empty Then Chaine4 = Match.Value Else Chaine4 = Chaine4 & " / " & Match.Value
                    ElseIf Match.Length = 5 Then
                        If Chaine5 = Empty Then Chaine5 = Match.Value Else Chaine5 = Chaine5 & " / " & Match.Value
                    End If
                Next Match
        End If
End With
If Cells(3, ActiveCell.Column) = "NCA" Then
    ResBis = Chaine4
ElseIf Cells(3, ActiveCell.Column) = "NCR" Then
    ResBis = Chaine5
End If
End Function

 

[patricktoulon]

re
rien que ca .Pattern = "\d{4,5}" déjà t'a tout faux a croire que tu n'a pas bien saisi la demande