let
A = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
B = Table.TransformColumns(A, {"Column1", each
[
a = Text.Split(_," "),
b = List.PositionOf(List.Transform(a, (x)=> Text.Length(x)), 7, Occurrence.Last),
c = List.Range(a,b + 1, 2),
d = if Text.Length(c{0}) = 1 then Text.Combine(c, " ") else c{0}
] [d]
})
in
B
Function Extract$(c As Range)
Dim i%
For i = 1 To Len(c)
With c.Characters(i, 1)
If .Font.Color <> vbBlack Then Extract = Extract & .Text
End With
Next
End FunctionBon d'accord alors utilisez ceci mais c'est bien moins compréhensible :Hello @job75
je crois que la coloration rouge des nombres à extraire a été mise par le demandeur pour montrer ce qu'il souhaitait récupérer
Function Extract$(x$)
Dim i%, j%
i = InStrRev(x, ",", Len(x) - 3)
For j = i - 1 To 1 Step -1
If Not IsNumeric(Mid(x, j, i - j + 1)) Then Extract = Mid(x, j + 7, i - j - 4): Exit For
Next
End FunctionFunction Extract$(x$)
Dim i%, j%
i = InStrRev(x, ",", Len(x) - 3)
j = InStrRev(x, ",", i - 1)
Extract = Mid(x, j + 7, i - j - 4)
End Function
Function ExtractNum$(n$)
Dim regex As Object
Dim matches As Object
ExtractNum = ""
On Error Resume Next
Set regex = CreateObject("VBScript.RegExp")
regex.Pattern = "\d* \d+,\d+"
regex.Global = True
Set matches = regex.Execute(n)
If matches.Count > 1 Then
ExtractNum = Trim(matches(matches.Count - 2).Value)
End If
End Function
