XL 2019 Formule pour isoler des nombres

[ya_v_ka]

Salut tout le monde,

D'abord, meilleurs voeux pour 2025 !

Je ne m'en sors pas en formule ; Après avoir testé TROUVE, GAUCHE, SUBSTITUE, CNUM etc et leurs combinaisons, je n'arrive pas au résultat souhaité qui est :

en A les données, ensuite chaque nombre séparément dans une colonne avec une formule valable pour chaque cas de figure...

Si quelqu'un a un semblant de piste, voir mieux...

Merci d'avance

Y'av

 

[sylvanu]

Bonsoir ya_v_ka,
Peut être une approche en VBA avec cette fonction:

Function Sépare(N, i)
Dim T, Place%, j%
T = Split(N, ","): Place = 1
For j = 0 To UBound(T)
    If IsNumeric(T(j)) And Place = i Then
        Sépare = T(j): Exit Function
    ElseIf IsNumeric(T(j)) Then
        Place = Place + 1
    End If
Next j
If Sépare = 0 Then Sépare = ""
End Function

La syntaxe est :

=Sépare(Chaine;Position)

 

[job75]

Bonsoir ya_v_ka, sylvanu,

Une autre solution VBA :

Sub Extraire()
Dim P As Range
Application.ScreenUpdating = False
With ActiveSheet.UsedRange.Columns(1).Offset(1)
    Set P = .Offset(, 1).Resize(, Columns.Count - .Column)
    P.ClearContents
    .TextToColumns .Offset(, 1), xlDelimited, Comma:=True 'commande Convertir
    On Error Resume Next 'si aucune SpecialCell
    P.SpecialCells(xlCellTypeConstants, 2).ClearContents
    P.SpecialCells(xlCellTypeBlanks).Delete xlToLeft
End With
End Sub

Bonne nuit.

 

[ya_v_ka]

Bonsoir Sylvanu & Job75,

Vous me dégoutez !... J'avais solutionné en VBA, mais bien plus laborieusement : Je prenais A dans machaine, y supprimais les espaces, extrayais jusqu'à la première virgule, soustrayais cet extrait pour raccourcir machaine, ôtais la virgule, si numérique le reportais et recommençais autant de fois que nécessaire !

Mais là c'est bien en formule que je cherche, le VBA m'étant interdit sur ce projet appelant à être partagé avec différent services étatiques n'acceptant pas les signatures externes...

Je garde vos solutions en mémoire pour moi ou pour un prochain projet interne.

Merci encore et bravo.

Amitiés

Y'av

 

[sylvanu]

Re, bonjour Job,
En formules cela dépasse mon niveau. sauf à rajouter plein de colonnes.
Mais les cadors des formules vous sortiront sûrement d'affaire.

 

[jurassic pork]

Hello,
grâce à la fonction magique DnaResize qui se trouve dans mon complément XlDnaLibJP, une fonction personnalisée qui renvoie un tableau peut s'étendre sur plusieurs cellules (même avec d'anciennes versions d'Excel). Le redimensionnement est automatique et les cellules qui sont impactées se transforment en formule matricielle.
Sans le DnaResize on a que la première valeur du tableau qui est renvoyée.
Pour que le DnaResize fonctionne il faut que les cellules qui sont impactées par le résultat ne soient pas déjà occupées.
Si on doit effacer la formule il faut supprimer toutes les cellules où se trouvent la formule matricielle.
Si on doit faire une modification dans la formule il faut passer par le bouton fx.

Voici par exemple une fonction personnalisée qui utilisent les expressions régulières pour extraire que les nombres d'un texte :

Function IsolerNombres(texte)
Dim regex As Object, Matches As Object, Match As Object, res(), i
'On Error Resume Next
Set regex = CreateObject("VBScript.RegExp")
regex.Pattern = "\d+": regex.Global = True   ' Motif \d+ -> un ou plusieurs chiffres
Set Matches = regex.Execute(texte)
ReDim res(1 To 1, 1 To Matches.Count)
i = 1
For Each Match In Matches
   res(1, i) = Match.Value
   i = i + 1
Next Match
IsolerNombres = res
Set regex = Nothing
End Function

Sub TstIsolerNombres()
 IsolerNombres "1, 8, 14, 17"
End Sub

Et voici ce que cela donne dans un Excel 2016 32 bits :

Sinon il y a bien le RegexExtract sous xl 365 (que je n'ai pas) mais qui me semble donne les résultats en vertical comme mon REGEXEXTRACTJP
Mais hélas comme tu n'as pas le droit au VBA et certainement aux compléments externes, cela ne va pas de servir . Il n'y a que le RegexExtract avec un Transpose par dessus qui pourrait fonctionner mais comme il me semble que tu n'es pas en Excel 365 tu ne peux pas l'utiliser. Sachant que mon REGEXEXTRACTJP n'accepte pas de Transpose par dessus.

Ami calmant, J.P

 

[JHA]

Bonjour à tous,

Peut-être une solution avec power query

JHA

 

[sylvanu]

Bonjour à tous,
Une autre méthode avec de l'huile de coude.
Simple mais fastidieuse s'il faut le faire souvent.

 

[Nain porte quoi]

Hello,

juste pour info, avec 365, voici une formule qui fait le job

=REGEX.EXTRAIRE(A1;"\d+";1)

 

[jurassic pork]

C'est ce que je disais plus haut mais le résultat est en Horizontal ou Vertical ? un Transpose dessus (je n'ai pas Excel 365) ?

 

[Nain porte quoi]

Horizontal

 

[ALS35]

Bonjour à tous,
Une possibilité à tester avec FILTRE.XML disponible depuis Excel 2013, en A2 et à recopier à droite et vers le bas :

=SIERREUR(INDEX(TRANSPOSE(SI(FILTRE.XML("<t><s>"&SUBSTITUE($A1;",";"</s><s>")&"</s></t>";"//s")>="A";"";FILTRE.XML("<t><s>"&SUBSTITUE($A1;",";"</s><s>")&"</s></t>";"//s")));COLONNE(A1));"")

Cordialement

 

[jurassic pork]

Horizontal

c'est bizarre c'est pas ce que je vois dans cette vidéo :

 

[ALS35]

Bonjour à tous de nouveau,
@jurassic pork
Dans le cas qui nous occupe, si tu fais
=REGEX.EXTRAIRE(A1:A9;"\d+"), tu as une formule unique et un résultat du premier match en vertical (comme A1:A9)
si tu fais
=REGEX.EXTRAIRE(A1;"\d+";1), tu as une formule par ligne et le résultat de tous les match en horizontal (avec le 3e paramètre à 1
(tu peux peut-être essayer avec excel en ligne)
Cordialement

 

[sylvanu]

Re,
En PJ avec une feuille cachée, donc très simple car tout est masqué.