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

 

[jurassic pork]

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)

Hello ALS35
Merci bien, je connaissais pas le Excel en ligne qui est gratuit (il suffit de se loguer avec son compte microsoft).
Cela permet de faire des essais sous Excel365. Effectivement le résultat est bien en horizontal. Le seul souci que j'ai eu c'était que j'avais les formules en Anglais (REGEXEXTRACT et mettre des , à la place des ; ) mais peut-être il faut configurer quelque chose pour l'avoir en français.
Ami calmant, J.P

 

[job75]

Hello,

Pour tester la macro de mon post #3 j'ai recopié la plage A2:A11 sur 50 000 lignes.

P.SpecialCells(xlCellTypeBlanks).Delete xlToLeft prend un temps fou, il faut sortir par Ctrl+Alt+Suppr.

C'est mieux avec cette macro qui s'exécute quand même en 40 secondes chez moi :

Sub Extraire()
Dim P As Range, col%
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
    For col = Intersect(P, ActiveSheet.UsedRange).Columns.Count To 1 Step -1
        P.Columns(col).SpecialCells(xlCellTypeConstants, 2).ClearContents
        P.Columns(col).SpecialCells(xlCellTypeBlanks).Delete xlToLeft
    Next
End With
End Sub

A+

 

[ya_v_ka]

Bonsoir tout le monde,

Merci à tous : Vous êtes comme toujours géniaux !

- JHA : Il faut vraiment que je me mette à Power Query...

- Nain porte quoi & Jurassic pork : Malheureusement pas ce REGEX et sans fonction perso (VBA).

- ALS35 : Magnifique formule qui me plaît beaucoup mais que dois encore essayer de comprendre (en décortiquant) = Trop fort ! Je pars sur ça...

- Sylvanu : Mes essais allaient dans le même sens (feuille ou colonnes masquées) mais je n'aurais jamais osé (n'y surtout pensé !) à cumuler autant de SUBSTITUE ! Je cherchais plus du côté : Si non num alors substitue par "" ! mais sans y arriver... Félicitations !!!

Et avec toutes vos idées et solutions je sais maintenant 1. que c'est possible et 2. que je vais pouvoir finaliser mon fichier grâce à vous.

MERCI à tous et amitiés à chacun.

Ya'v

 

[Amilo]

Bonjour à tous,
Une autre proposition Power Query
Et meilleurs voeux 2025
Cordialement

 

[jurassic pork]

Hello Amilo,
Beau travail mais le souci avec le Power Query c'est qu'il faut rafraîchir les requêtes pour qu'elles soient mise à jour. On pourrait mettre à jour ta requête lorsqu'il y a un événement de changement dans une des cellules source mais comme ya_v_k ne veut pas de code VBA ça va être difficile à faire. Mais peut-être que cela ne dérange pas ya_v_k de cliquer sur le bouton d'actualisation de requête ou de taper le raccourci quand il en a besoin.
D'autre part il faudrait savoir si les données source sont limitées à 4 nombres maximun par ligne car cela ne fonctionne pas si j'en rajoute un cinquième sur une ligne (ex 45 ) :

Ami calmant, J.P

 

[ALS35]

- ALS35 : Magnifique formule qui me plaît beaucoup mais que dois encore essayer de comprendre (en décortiquant) = Trop fort ! Je pars sur ça...

Bonjour,
Le principe c'est qu'on transforme la chaîne 1, 8, 14,17 en chaîne avec des balises xml avec le SUBSTITUE pour obtenir <t><s>1</s><s> 8</s><s> 14</s><s>17</s></t>
La fonction FILTRE.XML filtre alors les différentes occurrences et donne un résultat en colonne
La fonction SI > "A" permet d'éliminer les valeurs textuelles
La fonction TRANSPOSE permet de mettre le résultat en ligne
La fonction INDEX permet de prendre chaque élément de 1 à n avec le COLONNE(A1) qui varie quand on recopie la formule vers la droite
Cordialement

 

[alexga78]

Bonjour à tous,

pour ceux qui aiment le code M

let
A = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
B = (v)=>
    [
    x = List.Select(List.Transform(Text.Split(Text.From(v{0}),","), Text.Trim), each List.ContainsAny({"1".."9"}, Text.ToList(_))),
    y = Table.FromRows({x}, List.Transform({1..List.Count(x)}, each "Number." & Text.From(_)))
    ][y],
C = Table.Combine(Table.ToList(A, B))
in C

Bonne journée

 

[Amilo]

Bonjour à tous,
@jurassic pork ,
Vous avez raison de le souligner, c'est un classique que j'ai omis pour les colonnes dynamiques.
Ceci devrait fonctionner :

Cordialement

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Data", type text}}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Type modifié", "Test", each Text.Remove([Data], {"0".."9"})),
    Personnalisé1 = Table.AddColumn(#"Personnalisée ajoutée", "Col", each List.RemoveNulls(List.Transform(Text.SplitAny([Data], [Test]), Number.From))),
    #"Valeurs extraites" = Table.TransformColumns(Personnalisé1, {"Col", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    Personnalisé2 = Table.SplitColumn(#"Valeurs extraites", "Col", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), List.Transform({1..List.Max(Table.AddColumn(#"Valeurs extraites", "Temp", each List.Count(Text.Split([Col],"|")))[Temp])},each "Col." & Number.ToText(_))),
    #"Colonnes supprimées" = Table.RemoveColumns(Personnalisé2,{"Test"})
in
    #"Colonnes supprimées"

 

[job75]

Bonjour le forum,

Voici une solution par formule qui fonctionne sur toute version Excel.

Dans le fichier joint sélectionner B2 et définir les 4 noms deb fin Nbre N comme indiqué.

Formule en B2 à tirer vers la droite et vers le bas :

=INDEX(Nbre;PETITE.VALEUR(N;COLONNE()-1))

Touche F5 pour sélectionner les valeurs d'erreur et les effacer toutes.

Pour tester j'ai recopié A2:G11 sur 50 000 lignes en exécutant :

Sub Test()
Dim t
t = Timer
[A2:G11].Copy [A2:G50001]
MsgBox Timer - t
End Sub

La macro s'exécute en 27 secondes chez moi.

A+

 

[jurassic pork]

Hello job75,
houla tu nous proposes là de la haute technologie : des variables formule
Ami calmant, J.P

 

[job75]

J'ai simplifié le nom Nbre, la copie sur 50 000 lignes s'effectue en 14 secondes.

 

[jurassic pork]

job75 je ne sais pas si c'est le temps de calcul de tes formules que tu veux calculer mais avec une copie tu induis certainement du temps supplémentaire.

 

[job75]

job75 je ne sais pas si c'est le temps de calcul de tes formules que tu veux calculer mais avec une copie tu induis certainement du temps supplémentaire.

En fait la fonction INDIRECT est volatile donc deb et toutes les formules sont volatiles.

Après avoir créé les 50 000 lignes il suffit d'exécuter :

Sub Test2()
Dim t
t = Timer
Calculate
MsgBox Timer - t
End Sub

Pour le post #26 je trouve 13 secondes au lieu de 14 secondes.