XL 2016 Récupérer dans un libellé un mot avant une cassure

[Armand11]

Bonjour la communauté Exceldownload,

Tout d'abord meilleurs voeux pour cette nouvelle année.
J'ai besoin de votre aide pour récupérer en automatique un mot d'un libellé :

Voici mon libellé :
"Total Bill Variance//PO 1-23-001993//SUPP59 Thorlabs//AP4M3M"

Et je dois récupérer SUPP59 c'est à dire tous les SUPP et ce qu'il y a aprés jusquà la cassure c'est à dire l'espace avant "Thorlab".

J'ai essayé de combiner les formules STXT(A8;36;8) et ensuite un Gauche(A9;6) mais dans certain cas ca ne marche pas. Car en fait mes Codes Fournisseurs peuvent être : SUPP30 ou SUPP1 ou SUPP1984 (ils ne dépassent pas 8 caractères)

Merci beaucoup pour votre aide
Armand

 

[Hecatonchire]

Bonjour,

Texte en A2
=STXT(A2;CHERCHE("SUPP";A2);CHERCHE(" ";A2;CHERCHE("SUPP";A2))-CHERCHE("SUPP";A2))

version 365
=LET(c;A2;PosD;CHERCHE("SUPP";c); STXT(c;PosD;CHERCHE(" ";c;PosD)-PosD))

 

[mapomme]

Bonjour,

En supposant que le texte à extraire est quelconque mais c'est le premier mot après le deuxième // (texte en A1):
=SUPPRESPACE(GAUCHE(SUBSTITUE(STXT(A1;CHERCHE("[";SUBSTITUE(A1;"//";"[";2))+2;99);" ";REPT(" ";200));200))

 

[Armand11]

Bonjour,

Texte en A2
=STXT(A2;CHERCHE("SUPP";A2);CHERCHE(" ";A2;CHERCHE("SUPP";A2))-CHERCHE("SUPP";A2))

version 365
=LET(c;A2;PosD;CHERCHE("SUPP";c); STXT(c;PosD;CHERCHE(" ";c;PosD)-PosD))

C'est nickel ! merci beaucoup. Dans les deux cas ca fonctionne. C'est cool j'avais 70 000 lignes .... Excellente journée à vous

 

[Nain porte quoi]

Hello,

une autre version 365
=TEXTE.AVANT(TEXTE.APRES(A2;"//";2);" ")

 

[Hecatonchire]

Bonjour à tous,

Il faut vraiment que j'y pense à TEXTE.AVANT, TEXTE.APRES !

Pour le plaisir, basée sur la solution de Nain porte quoi si la 2ème paires "//" n'est pas un critère valable
="SUPP"&TEXTE.AVANT(TEXTE.APRES(A2;"SUPP");" ")

 

[nullosse]

Salut,
Avec Excel365, on peut aussi utiliser les expressions régulières :

=REGEXEXTRACT(A2;"//([^/ ]+)\s+Thorlabs")

// : repère le début du segment
([^/ ]+) : capture une suite de caractères sans slash ni expace → ici SUPP59
\s+Thorlabs : s’arrête juste avant le mot Thorlabs

Nullosse

 

[Nain porte quoi]

@nullosse je crois que j'ai plus simple et inspiré de Hecatonchire
=REGEX.EXTRAIRE(A2;"SUPP\d+")
et je ne crois pas que "Thorlabs" soit systématiquement après

 

[patricktoulon]

bonjour à l'ancienne

=STXT(STXT(SUBSTITUE(A1;"//";"µ";2);CHERCHE("µ";SUBSTITUE(A1;"//";"µ";2); 1)+1;99);1;CHERCHE(" ";STXT(SUBSTITUE(A1;"//";"µ";2);CHERCHE("µ";SUBSTITUE(A1;"//";"µ";2); 1)+1;99);1))

ou encore plus vieux
je remplace les "//" par des O/F de balise s a , je remplace les espace par des O/F de balise e le tout encapsulé dans une balise q
et je vais chercher la balise e unique dans la 3eme a

=FILTRE.XML("<q><a><e>" &SUBSTITUE(SUBSTITUE(A1;"//";"</e></a><a><e>");" ";"</e><e>")&"</e></a></q>";"//a[position()=3]//e")

bon d'accords ce n'est pas aussi seduisant que texte.apres ou texte.avant mais bon

 

[nullosse]

pour Excel2016 une fonction personnalisée pour pouvoir utiliser les expressions régulières (à insérer dans un module standard VBA) :

Function RegexExtractVBA(Text As String, Pattern As String, Optional GroupIndex As Long = 1) As String
    Dim re As Object, matches As Object, m As Object
    Set re = CreateObject("VBScript.RegExp")
    re.Pattern = Pattern
    re.Global = False
    re.IgnoreCase = False
    If re.Test(Text) Then
        Set matches = re.Execute(Text)
        Set m = matches(0)
        If GroupIndex <= m.SubMatches.Count Then
            RegexExtractVBA = m.SubMatches(GroupIndex - 1)
        Else
            RegexExtractVBA = ""
        End If
    Else
        RegexExtractVBA = ""
    End If
End Function

Voici l'expression régulière (plus générique que les précédentes) pour récupérer le premier mot alphanumérique qui se trouve après le deuxième // :

=RegexExtractVBA(A2;"//[^/]*//(\w+)\s+")

// → premier séparateur
[^/]*// → jusqu’au deuxième //
(\w+) → mot alphanumérique capturé → SUPP59
\s+ → au moins un espace après (SUPP59␠Thorlabs)

A noter que dans le cas présent on peut faire plus simple sans utiliser d'expressions régulières avec une fonction personnalisée qui emploie Split :

Function TextApresNsep(txt As String, sep As String, n As Long) As String
    TextApresNsep = Split(Trim(Split(txt, sep)(n)), " ")(0)
End Function

=TextApresNsep(A2;"//";2)

 

[patricktoulon]

Bonjour @nullosse je l'ai fait comme ceci

Sub test()
chaine$ = "Total Bill Variance//PO 1-23-001993//SUPP59 Thorlabs//AP4M3M"
MsgBox RegExtract(chaine, "//([A-Za-z0-9]+)\s[^/]*", 2)

End Sub
Function RegExtract(chaine, Pattern As String, Optional Index As Long = 1) As String
    Dim reg As Object, matches As Object, m As Object
    chaine = CStr(chaine)
    With CreateObject("VBScript.RegExp")
    .Pattern = Pattern:    .Global = True:    .IgnoreCase = False
    If .test(chaine) Then
       Set matchs = .Execute(chaine)
          If matchs.Count > 0 Then
            RegExtract = matchs(Index - 1).submatches(0)
        End If
     End If
End With
End Function

 

[job75]

Bonjour le forum,

Avec cette fonction VBA s'il y a plusieurs occurrences dans le même texte elles sont toutes récupérées :

Function Extrait$(txt$, crit1$, crit2$)
Dim L1%, L2%, L, i%, j%, a(), n%
L1 = Len(crit1)
L2 = Len(crit2)
L = Len(txt)
For i = 1 To L
    If Mid(txt, i, L1) = crit1 Then
        For j = i + L1 To L
            If Mid(txt, j, L1) = crit1 Then i = j - 1: GoTo 1
            If Mid(txt, j, L2) = crit2 Then
                ReDim Preserve a(n)
                a(n) = Mid(txt, i, j + L2 - i) 'avec le 2ème critère (espace)
                n = n + 1
                i = j
                Exit For
            End If
        Next j
    End If
1 Next i
If n Then Extrait = Join(a, "") 'concaténation sans séparateur
End Function

A+

 

[job75]

Pour tester j'ai rendu la fonction volatile et recopié le tableau A2:B5 sur 70 000 lignes.

Les formules se recalculent chez moi en 1 seconde.

 

[Nain porte quoi]

Ho le joli GoTo 😀

 

[nullosse]

Ho le joli GoTo 😀

Salut,
malheureusement , il n'y a pas de Continue pour les boucles en VBA.
Nullosse