Retourner une correspondance regex dans VBA (excel)

J'essaie d'écrire une fonction pour Excel 2010 qui prendra une cellule de text non structuré, chercher quelque chose appelé une valeur sdi et, s'il est trouvé, renvoyer ce numéro. La valeur sdi apparaîtra comme sdi ####. Ce que je veux, c'est de renvoyer sdi et les nombres spécifiques qui le suivent, alors si la cellule contient "un certain text sdi 1234 un peu plus de text", la fonction renverra sdi 1234.

C'est ma fonction:

Function SdiTest(LookIn As Ssortingng) As Ssortingng Dim temp As Ssortingng Dim STA As Object temp = "" Set SDI = CreateObject("VBScript.RegExp") SDI.IgnoreCase = True SDI.Pattern = "sdi [1-9]*" SDI.Global = True If SDI.Test(LookIn) Then temp = SDI.Execute(LookIn) End If SdiTest = temp End Function

S'il n'y a pas de numéro sdi, il n'entre jamais dans l'instruction if et ne returnne pas la string vide. S'il y a un numéro sdi, j'obtiens #VALUE!

Qu'est-ce qui me manque?

Oui, VBScript est activé. De plus, je trouve frustrant d'utiliser Regex dans VBA, et difficile de find des informations utiles en ligne. Les liens vers de bonnes ressources en ligne seraient appréciés.

Je vous remercie

Vous devez accéder aux correspondances pour get le numéro SDI. Voici une fonction qui le fera (en supposant qu'il n'y ait qu'un seul numéro SDI par cellule).

Pour le regex, j'ai utilisé "sdi suivi d'un espace et d'un ou plusieurs nombres". Vous avez eu "sdi suivi d'un espace et de zéro ou plus de nombres". Vous pouvez simplement modifier le + à * dans mon model pour revenir à ce que vous aviez.

 Function ExtractSDI(ByVal text As Ssortingng) As Ssortingng Dim result As Ssortingng Dim allMatches As Object Dim RE As Object Set RE = CreateObject("vbscript.regexp") RE.pattern = "(sdi \d+)" RE.Global = True RE.IgnoreCase = True Set allMatches = RE.Execute(text) If allMatches.count <> 0 Then result = allMatches.Item(0).submatches.Item(0) End If ExtractSDI = result End Function Fonction ExtractSDI (ByVal text As Ssortingng) As Ssortingng Function ExtractSDI(ByVal text As Ssortingng) As Ssortingng Dim result As Ssortingng Dim allMatches As Object Dim RE As Object Set RE = CreateObject("vbscript.regexp") RE.pattern = "(sdi \d+)" RE.Global = True RE.IgnoreCase = True Set allMatches = RE.Execute(text) If allMatches.count <> 0 Then result = allMatches.Item(0).submatches.Item(0) End If ExtractSDI = result End Function Dim result As Ssortingng Function ExtractSDI(ByVal text As Ssortingng) As Ssortingng Dim result As Ssortingng Dim allMatches As Object Dim RE As Object Set RE = CreateObject("vbscript.regexp") RE.pattern = "(sdi \d+)" RE.Global = True RE.IgnoreCase = True Set allMatches = RE.Execute(text) If allMatches.count <> 0 Then result = allMatches.Item(0).submatches.Item(0) End If ExtractSDI = result End Function Dim allMatches As Object Function ExtractSDI(ByVal text As Ssortingng) As Ssortingng Dim result As Ssortingng Dim allMatches As Object Dim RE As Object Set RE = CreateObject("vbscript.regexp") RE.pattern = "(sdi \d+)" RE.Global = True RE.IgnoreCase = True Set allMatches = RE.Execute(text) If allMatches.count <> 0 Then result = allMatches.Item(0).submatches.Item(0) End If ExtractSDI = result End Function Dim RE As Object Function ExtractSDI(ByVal text As Ssortingng) As Ssortingng Dim result As Ssortingng Dim allMatches As Object Dim RE As Object Set RE = CreateObject("vbscript.regexp") RE.pattern = "(sdi \d+)" RE.Global = True RE.IgnoreCase = True Set allMatches = RE.Execute(text) If allMatches.count <> 0 Then result = allMatches.Item(0).submatches.Item(0) End If ExtractSDI = result End Function Définir RE = CreateObject ("vbscript.regexp") Function ExtractSDI(ByVal text As Ssortingng) As Ssortingng Dim result As Ssortingng Dim allMatches As Object Dim RE As Object Set RE = CreateObject("vbscript.regexp") RE.pattern = "(sdi \d+)" RE.Global = True RE.IgnoreCase = True Set allMatches = RE.Execute(text) If allMatches.count <> 0 Then result = allMatches.Item(0).submatches.Item(0) End If ExtractSDI = result End Function RE.pattern = "(sdi \ d +)" Function ExtractSDI(ByVal text As Ssortingng) As Ssortingng Dim result As Ssortingng Dim allMatches As Object Dim RE As Object Set RE = CreateObject("vbscript.regexp") RE.pattern = "(sdi \d+)" RE.Global = True RE.IgnoreCase = True Set allMatches = RE.Execute(text) If allMatches.count <> 0 Then result = allMatches.Item(0).submatches.Item(0) End If ExtractSDI = result End Function RE.Global = True Function ExtractSDI(ByVal text As Ssortingng) As Ssortingng Dim result As Ssortingng Dim allMatches As Object Dim RE As Object Set RE = CreateObject("vbscript.regexp") RE.pattern = "(sdi \d+)" RE.Global = True RE.IgnoreCase = True Set allMatches = RE.Execute(text) If allMatches.count <> 0 Then result = allMatches.Item(0).submatches.Item(0) End If ExtractSDI = result End Function RE.IgnoreCase = True Function ExtractSDI(ByVal text As Ssortingng) As Ssortingng Dim result As Ssortingng Dim allMatches As Object Dim RE As Object Set RE = CreateObject("vbscript.regexp") RE.pattern = "(sdi \d+)" RE.Global = True RE.IgnoreCase = True Set allMatches = RE.Execute(text) If allMatches.count <> 0 Then result = allMatches.Item(0).submatches.Item(0) End If ExtractSDI = result End Function Définir allMatches = RE.Execute (text) Function ExtractSDI(ByVal text As Ssortingng) As Ssortingng Dim result As Ssortingng Dim allMatches As Object Dim RE As Object Set RE = CreateObject("vbscript.regexp") RE.pattern = "(sdi \d+)" RE.Global = True RE.IgnoreCase = True Set allMatches = RE.Execute(text) If allMatches.count <> 0 Then result = allMatches.Item(0).submatches.Item(0) End If ExtractSDI = result End Function Si allMatches.count <> 0 Then Function ExtractSDI(ByVal text As Ssortingng) As Ssortingng Dim result As Ssortingng Dim allMatches As Object Dim RE As Object Set RE = CreateObject("vbscript.regexp") RE.pattern = "(sdi \d+)" RE.Global = True RE.IgnoreCase = True Set allMatches = RE.Execute(text) If allMatches.count <> 0 Then result = allMatches.Item(0).submatches.Item(0) End If ExtractSDI = result End Function result = allMatches.Item (0) .submatches.Item (0) Function ExtractSDI(ByVal text As Ssortingng) As Ssortingng Dim result As Ssortingng Dim allMatches As Object Dim RE As Object Set RE = CreateObject("vbscript.regexp") RE.pattern = "(sdi \d+)" RE.Global = True RE.IgnoreCase = True Set allMatches = RE.Execute(text) If allMatches.count <> 0 Then result = allMatches.Item(0).submatches.Item(0) End If ExtractSDI = result End Function ExtractSDI = résultat Function ExtractSDI(ByVal text As Ssortingng) As Ssortingng Dim result As Ssortingng Dim allMatches As Object Dim RE As Object Set RE = CreateObject("vbscript.regexp") RE.pattern = "(sdi \d+)" RE.Global = True RE.IgnoreCase = True Set allMatches = RE.Execute(text) If allMatches.count <> 0 Then result = allMatches.Item(0).submatches.Item(0) End If ExtractSDI = result End Function

Si une cellule peut avoir plus d'un numéro SDI que vous souhaitez extraire, voici ma fonction RegexExtract. Vous pouvez passer un troisième paramètre pour séparer chaque correspondance (comme les séparer par des virgules) et vous entrez manuellement le motif dans l'appel de la fonction réelle:

 Ex) =RegexExtract(A1, "(sdi \d+)", ", ")

Voici:

 Function RegexExtract(ByVal text As Ssortingng, _ ByVal extract_what As Ssortingng, _ Optional seperator As Ssortingng = "") As Ssortingng Dim i As Long, j As Long Dim result As Ssortingng Dim allMatches As Object Dim RE As Object Set RE = CreateObject("vbscript.regexp") RE.pattern = extract_what RE.Global = True Set allMatches = RE.Execute(text) For i = 0 To allMatches.count - 1 For j = 0 To allMatches.Item(i).submatches.count - 1 result = result & seperator & allMatches.Item(i).submatches.Item(j) Next Next If Len(result) <> 0 Then result = Right(result, Len(result) - Len(seperator)) End If RegexExtract = result End Function Fonction RegexExtract (ByVal text As Ssortingng, _ Function RegexExtract(ByVal text As Ssortingng, _ ByVal extract_what As Ssortingng, _ Optional seperator As Ssortingng = "") As Ssortingng Dim i As Long, j As Long Dim result As Ssortingng Dim allMatches As Object Dim RE As Object Set RE = CreateObject("vbscript.regexp") RE.pattern = extract_what RE.Global = True Set allMatches = RE.Execute(text) For i = 0 To allMatches.count - 1 For j = 0 To allMatches.Item(i).submatches.count - 1 result = result & seperator & allMatches.Item(i).submatches.Item(j) Next Next If Len(result) <> 0 Then result = Right(result, Len(result) - Len(seperator)) End If RegexExtract = result End Function ByVal extract_what As Ssortingng, _ Function RegexExtract(ByVal text As Ssortingng, _ ByVal extract_what As Ssortingng, _ Optional seperator As Ssortingng = "") As Ssortingng Dim i As Long, j As Long Dim result As Ssortingng Dim allMatches As Object Dim RE As Object Set RE = CreateObject("vbscript.regexp") RE.pattern = extract_what RE.Global = True Set allMatches = RE.Execute(text) For i = 0 To allMatches.count - 1 For j = 0 To allMatches.Item(i).submatches.count - 1 result = result & seperator & allMatches.Item(i).submatches.Item(j) Next Next If Len(result) <> 0 Then result = Right(result, Len(result) - Len(seperator)) End If RegexExtract = result End Function Séparateur optionnel As Ssortingng = "") As Ssortingng Function RegexExtract(ByVal text As Ssortingng, _ ByVal extract_what As Ssortingng, _ Optional seperator As Ssortingng = "") As Ssortingng Dim i As Long, j As Long Dim result As Ssortingng Dim allMatches As Object Dim RE As Object Set RE = CreateObject("vbscript.regexp") RE.pattern = extract_what RE.Global = True Set allMatches = RE.Execute(text) For i = 0 To allMatches.count - 1 For j = 0 To allMatches.Item(i).submatches.count - 1 result = result & seperator & allMatches.Item(i).submatches.Item(j) Next Next If Len(result) <> 0 Then result = Right(result, Len(result) - Len(seperator)) End If RegexExtract = result End Function Dim i As Long, j As Long Function RegexExtract(ByVal text As Ssortingng, _ ByVal extract_what As Ssortingng, _ Optional seperator As Ssortingng = "") As Ssortingng Dim i As Long, j As Long Dim result As Ssortingng Dim allMatches As Object Dim RE As Object Set RE = CreateObject("vbscript.regexp") RE.pattern = extract_what RE.Global = True Set allMatches = RE.Execute(text) For i = 0 To allMatches.count - 1 For j = 0 To allMatches.Item(i).submatches.count - 1 result = result & seperator & allMatches.Item(i).submatches.Item(j) Next Next If Len(result) <> 0 Then result = Right(result, Len(result) - Len(seperator)) End If RegexExtract = result End Function Dim result As Ssortingng Function RegexExtract(ByVal text As Ssortingng, _ ByVal extract_what As Ssortingng, _ Optional seperator As Ssortingng = "") As Ssortingng Dim i As Long, j As Long Dim result As Ssortingng Dim allMatches As Object Dim RE As Object Set RE = CreateObject("vbscript.regexp") RE.pattern = extract_what RE.Global = True Set allMatches = RE.Execute(text) For i = 0 To allMatches.count - 1 For j = 0 To allMatches.Item(i).submatches.count - 1 result = result & seperator & allMatches.Item(i).submatches.Item(j) Next Next If Len(result) <> 0 Then result = Right(result, Len(result) - Len(seperator)) End If RegexExtract = result End Function Dim allMatches As Object Function RegexExtract(ByVal text As Ssortingng, _ ByVal extract_what As Ssortingng, _ Optional seperator As Ssortingng = "") As Ssortingng Dim i As Long, j As Long Dim result As Ssortingng Dim allMatches As Object Dim RE As Object Set RE = CreateObject("vbscript.regexp") RE.pattern = extract_what RE.Global = True Set allMatches = RE.Execute(text) For i = 0 To allMatches.count - 1 For j = 0 To allMatches.Item(i).submatches.count - 1 result = result & seperator & allMatches.Item(i).submatches.Item(j) Next Next If Len(result) <> 0 Then result = Right(result, Len(result) - Len(seperator)) End If RegexExtract = result End Function Dim RE As Object Function RegexExtract(ByVal text As Ssortingng, _ ByVal extract_what As Ssortingng, _ Optional seperator As Ssortingng = "") As Ssortingng Dim i As Long, j As Long Dim result As Ssortingng Dim allMatches As Object Dim RE As Object Set RE = CreateObject("vbscript.regexp") RE.pattern = extract_what RE.Global = True Set allMatches = RE.Execute(text) For i = 0 To allMatches.count - 1 For j = 0 To allMatches.Item(i).submatches.count - 1 result = result & seperator & allMatches.Item(i).submatches.Item(j) Next Next If Len(result) <> 0 Then result = Right(result, Len(result) - Len(seperator)) End If RegexExtract = result End Function Définir RE = CreateObject ("vbscript.regexp") Function RegexExtract(ByVal text As Ssortingng, _ ByVal extract_what As Ssortingng, _ Optional seperator As Ssortingng = "") As Ssortingng Dim i As Long, j As Long Dim result As Ssortingng Dim allMatches As Object Dim RE As Object Set RE = CreateObject("vbscript.regexp") RE.pattern = extract_what RE.Global = True Set allMatches = RE.Execute(text) For i = 0 To allMatches.count - 1 For j = 0 To allMatches.Item(i).submatches.count - 1 result = result & seperator & allMatches.Item(i).submatches.Item(j) Next Next If Len(result) <> 0 Then result = Right(result, Len(result) - Len(seperator)) End If RegexExtract = result End Function RE.pattern = extract_what Function RegexExtract(ByVal text As Ssortingng, _ ByVal extract_what As Ssortingng, _ Optional seperator As Ssortingng = "") As Ssortingng Dim i As Long, j As Long Dim result As Ssortingng Dim allMatches As Object Dim RE As Object Set RE = CreateObject("vbscript.regexp") RE.pattern = extract_what RE.Global = True Set allMatches = RE.Execute(text) For i = 0 To allMatches.count - 1 For j = 0 To allMatches.Item(i).submatches.count - 1 result = result & seperator & allMatches.Item(i).submatches.Item(j) Next Next If Len(result) <> 0 Then result = Right(result, Len(result) - Len(seperator)) End If RegexExtract = result End Function RE.Global = True Function RegexExtract(ByVal text As Ssortingng, _ ByVal extract_what As Ssortingng, _ Optional seperator As Ssortingng = "") As Ssortingng Dim i As Long, j As Long Dim result As Ssortingng Dim allMatches As Object Dim RE As Object Set RE = CreateObject("vbscript.regexp") RE.pattern = extract_what RE.Global = True Set allMatches = RE.Execute(text) For i = 0 To allMatches.count - 1 For j = 0 To allMatches.Item(i).submatches.count - 1 result = result & seperator & allMatches.Item(i).submatches.Item(j) Next Next If Len(result) <> 0 Then result = Right(result, Len(result) - Len(seperator)) End If RegexExtract = result End Function Définir allMatches = RE.Execute (text) Function RegexExtract(ByVal text As Ssortingng, _ ByVal extract_what As Ssortingng, _ Optional seperator As Ssortingng = "") As Ssortingng Dim i As Long, j As Long Dim result As Ssortingng Dim allMatches As Object Dim RE As Object Set RE = CreateObject("vbscript.regexp") RE.pattern = extract_what RE.Global = True Set allMatches = RE.Execute(text) For i = 0 To allMatches.count - 1 For j = 0 To allMatches.Item(i).submatches.count - 1 result = result & seperator & allMatches.Item(i).submatches.Item(j) Next Next If Len(result) <> 0 Then result = Right(result, Len(result) - Len(seperator)) End If RegexExtract = result End Function Pour i = 0 à allMatches.count - 1 Function RegexExtract(ByVal text As Ssortingng, _ ByVal extract_what As Ssortingng, _ Optional seperator As Ssortingng = "") As Ssortingng Dim i As Long, j As Long Dim result As Ssortingng Dim allMatches As Object Dim RE As Object Set RE = CreateObject("vbscript.regexp") RE.pattern = extract_what RE.Global = True Set allMatches = RE.Execute(text) For i = 0 To allMatches.count - 1 For j = 0 To allMatches.Item(i).submatches.count - 1 result = result & seperator & allMatches.Item(i).submatches.Item(j) Next Next If Len(result) <> 0 Then result = Right(result, Len(result) - Len(seperator)) End If RegexExtract = result End Function Pour j = 0 À allMatches.Item (i) .submatches.count - 1 Function RegexExtract(ByVal text As Ssortingng, _ ByVal extract_what As Ssortingng, _ Optional seperator As Ssortingng = "") As Ssortingng Dim i As Long, j As Long Dim result As Ssortingng Dim allMatches As Object Dim RE As Object Set RE = CreateObject("vbscript.regexp") RE.pattern = extract_what RE.Global = True Set allMatches = RE.Execute(text) For i = 0 To allMatches.count - 1 For j = 0 To allMatches.Item(i).submatches.count - 1 result = result & seperator & allMatches.Item(i).submatches.Item(j) Next Next If Len(result) <> 0 Then result = Right(result, Len(result) - Len(seperator)) End If RegexExtract = result End Function result = result & seperator & allMatches.Item (i) .submatches.Item (j) Function RegexExtract(ByVal text As Ssortingng, _ ByVal extract_what As Ssortingng, _ Optional seperator As Ssortingng = "") As Ssortingng Dim i As Long, j As Long Dim result As Ssortingng Dim allMatches As Object Dim RE As Object Set RE = CreateObject("vbscript.regexp") RE.pattern = extract_what RE.Global = True Set allMatches = RE.Execute(text) For i = 0 To allMatches.count - 1 For j = 0 To allMatches.Item(i).submatches.count - 1 result = result & seperator & allMatches.Item(i).submatches.Item(j) Next Next If Len(result) <> 0 Then result = Right(result, Len(result) - Len(seperator)) End If RegexExtract = result End Function Si Len (résultat) <> 0 Puis Function RegexExtract(ByVal text As Ssortingng, _ ByVal extract_what As Ssortingng, _ Optional seperator As Ssortingng = "") As Ssortingng Dim i As Long, j As Long Dim result As Ssortingng Dim allMatches As Object Dim RE As Object Set RE = CreateObject("vbscript.regexp") RE.pattern = extract_what RE.Global = True Set allMatches = RE.Execute(text) For i = 0 To allMatches.count - 1 For j = 0 To allMatches.Item(i).submatches.count - 1 result = result & seperator & allMatches.Item(i).submatches.Item(j) Next Next If Len(result) <> 0 Then result = Right(result, Len(result) - Len(seperator)) End If RegexExtract = result End Function result = Right (résultat, Len (résultat) - Len (seperator)) Function RegexExtract(ByVal text As Ssortingng, _ ByVal extract_what As Ssortingng, _ Optional seperator As Ssortingng = "") As Ssortingng Dim i As Long, j As Long Dim result As Ssortingng Dim allMatches As Object Dim RE As Object Set RE = CreateObject("vbscript.regexp") RE.pattern = extract_what RE.Global = True Set allMatches = RE.Execute(text) For i = 0 To allMatches.count - 1 For j = 0 To allMatches.Item(i).submatches.count - 1 result = result & seperator & allMatches.Item(i).submatches.Item(j) Next Next If Len(result) <> 0 Then result = Right(result, Len(result) - Len(seperator)) End If RegexExtract = result End Function RegexExtract = résultat Function RegexExtract(ByVal text As Ssortingng, _ ByVal extract_what As Ssortingng, _ Optional seperator As Ssortingng = "") As Ssortingng Dim i As Long, j As Long Dim result As Ssortingng Dim allMatches As Object Dim RE As Object Set RE = CreateObject("vbscript.regexp") RE.pattern = extract_what RE.Global = True Set allMatches = RE.Execute(text) For i = 0 To allMatches.count - 1 For j = 0 To allMatches.Item(i).submatches.count - 1 result = result & seperator & allMatches.Item(i).submatches.Item(j) Next Next If Len(result) <> 0 Then result = Right(result, Len(result) - Len(seperator)) End If RegexExtract = result End Function

* S'il vous plaît noter que j'ai pris "RE.IgnoreCase = True" hors de mon RegexExtract, mais vous pouvez l'append, ou même l'append en option 4ème paramètre si vous le souhaitez.