XL 2021 supprimer des signes

[bobafric]

Bonjour
Comment supprimer ou remplacer par un espace les signes qui séparent les nombres dans les cellules

 

[bobafric]

Je suis arrivé à le faire par la commande remplacer mais un signe après l'autre, est ce possible en VBA? Merci

 

[JHA]

Bonjour à tous,

Un essai avec power query

JHA

 

[jurassic pork]

Hello,
en VBA une fonction personnalisée avec un Regex.Replace (le RegexReplace existe en formule dans Excel 365)

Function RegexReplace$(texte$, pattern$, replacement$)
Dim regex As Object
On Error Resume Next
Set regex = CreateObject("VBScript.RegExp")
regex.pattern = pattern
regex.Global = True
RegexReplace = regex.Replace(texte, replacement)
regex.pattern = "\s+"
' on remplace des espaces multiples par un seul espace
RegexReplace = regex.Replace(RegexReplace, " ")
Set regex = Nothing
End Function

Ami calmant, J.P

 

[AtTheOne]

Bonsoir à toutes & à tous, à @bobafric , @JHA , @jurassic pork
Puisque la version utilisée est EXCEL2021 on peut le faire par formule :

=CONCAT(LET(T;B2;
            C;STXT(T;SEQUENCE(NBCAR(T));1);
            SI((CODE(C)<48)+(CODE(C)>57);" ";C)))

Voir le fichier joint
À bientôt

 

[jurassic pork]

Hello AtTheOne,
apparemment bobafric veut aussi réduire les espaces qui se suivent à un seul espace après remplacement de tous les caractères, ce que fait ma fonction personnalisée.
[EDIT] Oops je suis sur un Ordi qui n'a que Excel 2019 peut-être que ta formule le fait !
et ma fonction personnalisée peut être simplifiée :

Function RegexReplace$(texte$, pattern$, replacement$)
Dim regex As Object
On Error Resume Next
Set regex = CreateObject("VBScript.RegExp")
regex.pattern = pattern
regex.Global = True
RegexReplace = regex.Replace(texte, replacement)
Set regex = Nothing
End Function

en utilisant ceci :

=RegexReplace(B2;"[#&\-\+\s]+";" ")

Explication du motif : on remplace les caractères qui se trouvent au moins une fois entre crochet par un espace sachant que le \- représente le - (échappement) le \+ le + et le \s les caractères d'espacement comme par exemple l'espace ou tab. Le + derrière les crochets signifiant au moins un (un ou plus).
Ami calmant, J.P

 

[bobafric]

Hello AtTheOne,
apparemment bobafric veut aussi réduire les espaces qui se suivent à un seul espace après remplacement de tous les caractères, ce que fait ma fonction personnalisée.
[EDIT] Oops je suis sur un Ordi qui n'a que Excel 2019 peut-être que ta formule le fait !
et ma fonction personnalisée peut être simplifiée :

Function RegexReplace$(texte$, pattern$, replacement$)
Dim regex As Object
On Error Resume Next
Set regex = CreateObject("VBScript.RegExp")
regex.pattern = pattern
regex.Global = True
RegexReplace = regex.Replace(texte, replacement)
Set regex = Nothing
End Function

en utilisant ceci :

=RegexReplace(B2;"[#&\-\+\s]+";" ")

Explication du motif : on remplace les caractères qui se trouvent au moins une fois entre crochet par un espace sachant que le \- représente le - (échappement) le \+ le + et le \s les caractères d'espacement comme par exemple l'espace ou tab. Le + derrière les crochets signifiant au moins un (un ou plus).
Ami calmant, J.P

 

[bobafric]

Salut et merci à tous pour votre travail. Quel forum magnifique!!! Bon dimanche

 

[AtTheOne]

bonjour à toutes & à tous,
bonjour @bobafric et merci pour le retour
bonjour @jurassic pork

Hello AtTheOne,
apparemment bobafric veut aussi réduire les espaces qui se suivent à un seul espace

Oops je suis sur un Ordi qui n'a que Excel 2019 peut-être que ta formule le fait !

Non je te rassure elle ne le fait pas, je cherche quelque chose qui le fasse,

À bientôt

 

[AtTheOne]

Re,
Ça y est j'ai trouvé, plus d'espaces consécutifs avec cette formule :

=JOINDRE.TEXTE(" ";VRAI;SIERREUR(FILTRE.XML("<T><C>"&CONCAT(LET(T;B2;C;STXT(T;SEQUENCE(NBCAR(T));1);SI((CODE(C)<48)+(CODE(C)>57);"</C><C>";C)))&"</C></T>";"//C");""))

(à partir d'EXCEL2021)

À bientôt

 

[job75]

Bonjour le forum,

Juste une remarque pour jurassic pork

CreateObject("VBScript.RegExp") prend du temps, il vaut mieux utiliser cette fonction VBA :

Function Epure(x$, sep$)
Dim i%
For i = 1 To Len(x)
    If Not IsNumeric(Mid(x, i, 1)) Then Mid(x, i, 1) = " "
Next
Epure = Application.Trim(x) 'SUPPRESPACE
Epure = Replace(Epure, " ", sep)
End Function

A+

 

[jurassic pork]

Juste une remarque pour jurassic pork

CreateObject("VBScript.RegExp") prend du temps, il vaut mieux utiliser cette fonction VBA :

Hello job75,
c'est sûr que cela est nettement moins rapide mais cela dépend du nombre d'utilisation de la formule et l'avantage c'est que c'est plus souple : on peut changer le motif dans les paramètres de la fonction.
Et je serais curieux de savoir si quelqu'un a un Excel 365 , si il peut utiliser la fonction formule RegexReplace pour savoir le temps que cela prends.

 

[mapomme]

Bonjour à tous 🙂 ,

Allez! Pour le fun, une p'tite dernière :

Function QueLesChiffres$(x$)
Dim c$, i&, y$
   For i = 1 To Len(x): c = Mid(x, i, 1): y = y & IIf(c Like "#", c, " "): Next
   Do: i = Len(y): y = Replace(y, "  ", " "): Loop Until i = Len(y)
   QueLesChiffres = Trim(y)
End Function

 

[jurassic pork]

job75, bon j'ai fait des mesures de performance avec une chaîne de 23 caractères en entrée :
Epure : 60µs
RegexReplace : 500µs
Comme c'est la création de l'objet Regex qui plombe la fonction RegexReplace, en créant l'objet en Global en Early Binding, la création ne se fait qu'une seule fois. Voici le code :

Global Regex As New RegExp
Function RegexReplace$(texte$, pattern$, replacement$)
On Error Resume Next
Regex.pattern = pattern
Regex.Global = True
RegexReplace = Regex.Replace(texte, replacement)
End Function

avec ce code le temps moyen de la fonction RegexReplace est de 30µs (avec 23 caractères)
[EDIT] Avec la fonction QueLesChiffres de mapomme, j'ai 40 µs
et comme maPomme ne fait que les chiffres le Regex peut être alors :

=RegexReplace(B2;"[^\d]+";" ")

on remplace tout ce qui n'est pas chiffre

 

[job75]

Hello mapomme,

Pour tester j'ai recopié les lignes 2:3 sur 100 000 lignes.

Et j'ai rendu les formules volatiles en ajoutant T(ALEA())

Les 100 000 formules se recalculent en :

- 1,9 seconde avec la fonction du post #11

- 1,6 seconde avec la fonction du post #13

- 1,1 seconde avec la fonction du post #14.

A+