XL 2019 supprimer les espaces + les lettres et garder les chiffres

[Sofiane Boukecha]

Bonjour tout le monde,

svp, j'ai une colonne qui est constituée de " lettres + chiffres + espaces". moi j'aimerai garder uniquement les chiffres.

merci à vous.

 

[Sofiane Boukecha]

Hello

Peut etre ai-je loupé un tour, mais les formules fonctionnent si:
o Il y a un espace avant le chiffre
o il n'y a pas de lettre après le chiffre

je veux pas être pénible (encore que), mais ce n'etait pas l'énoncé.

constituée de " lettres + chiffres + espaces​

(c'etait par contre le fichier exemple)

bon, bref, si ca fonctionne tant mieux, mais PowerQuery j'aime pas. J'ai tjs des problèmes de dates mal formatées avec.

merci beaucoup pour votre retour,
désolé, effectivement mon fichier exemple n'était pas complet, il y'a bien plusieurs possibilités (avec plusieurs espaces, ou pas d'espaces). avec la solution du Power Query ça marche très bien (même si je connais pas encore son fonctionnement exacte) ça me pousse à creuser un peu plus de ce coté là.

merci à vous

 

[Sofiane Boukecha]

Salut 🙂

Si on veut garder les chiffres du dernier mot (quelque soit le nombre d'espaces, yc le(s) zéro(s) en tête du dernier mot, on pourra utiliser:

=DROITE(SUBSTITUE([@ID];" ";REPT(" ";250));250)

Si on veut le"nombre", on peut utiliser:

=CNUM(DROITE(SUBSTITUE([@ID];" ";REPT(" ";250));250))

merci beaucoup pour la réponse,
une formule que j'aime bien, c'est plus simple, je crois que je vais l'utiliser dans un autre exemple. par ce qu'avec la solution du power Query ça me permet de garder aussi la partie qui est en lettre.

merci a vous

 

[job75]

Bonjour à tous,

Depuis Excel 2016 il existe la fonction JOINDRE.TEXTE.

Voyez le fichier joint et cette formule matricielle en E4 :

=JOINDRE.TEXTE("";VRAI;SIERREUR(--STXT(B4;LIGNE(INDIRECT("1:"&NBCAR(B4)));1);""))

à valider par Ctrl+Maj+Entrée.

A+

 

[Sofiane Boukecha]

Bonjour à tous,

Depuis Excel 2016 il existe la fonction JOINDRE.TEXTE.

Voyez le fichier joint et cette formule matricielle en E4 :

=JOINDRE.TEXTE("";VRAI;SIERREUR(--STXT(B4;LIGNE(INDIRECT("1:"&NBCAR(B4)));1);""))

à valider par Ctrl+Maj+Entrée.

A+

bonjour,
c'est parfait comme solution, merci beaucoup.
y'a t'il une formule de ce genre pour faire le contraire, c'est à dire garder les "lettres" et supprimer les "espaces et les chiffres"

merci encore

 

[job75]

Bonjour Sofiane Boukecha, le fil,

y'a t'il une formule de ce genre pour faire le contraire, c'est à dire garder les "lettres" et supprimer les "espaces et les chiffres"

Oui mais faire le contraire c'est conserver tout ce qui n'est pas un chiffre (espace, tiret etc).

La formule matricielle est plus longue car on ne peut pas utiliser SIERREUR :

=JOINDRE.TEXTE("";VRAI;SI(ESTERR(-STXT(B4;LIGNE(INDIRECT("1:"&NBCAR(B4)));1));STXT(B4;LIGNE(INDIRECT("1:"&NBCAR(B4)));1);""))

A+

 

[Staple1600]

Bonjour le fil

Comme je n'ai pas Excel 2019 chez moi (mais 2013), je ne dispose pas de JOINDRE.TEXTE
Alors toujours en utilisant FILTRE.XML
(je suis parti du dernier fichier déposé par job75)

NB: Pour reproduire le test, saisir la formule ci-dessous en E4

=SIERREUR(INDEX(TRANSPOSE(FILTRE.XML("<t><s>"&SUBSTITUE($B4;"-";"</s><s>")&"</s></t>";"//s[translate(.,'1234567890','')=.]"));1;COLONNE()-4);"")

Puis tout en restant dans la barre de formule, sélectionnez F4 et G4 puis valider par CRTL+SHIFT+ENTREE

Sinon lancer cette petite macro qui fera le boulot

Sub Insertion_Formule()
Range("E4:G4").FormulaArray = _
"=IFERROR(INDEX(TRANSPOSE(FILTERXML(""<t><s>""&SUBSTITUTE(B4,""-"",""</s><s>"")&""</s></t>"",""//s[translate(.,'1234567890','')=.]"")),1,COLUMN()-4),"""")"
Range("E4:G6").FillDown
End Sub

 

[job75]

Bonjour Sofiane Boukecha, le fil,

Si l'on veut ne garder que les lettres, avec ou sans accents, on peut utiliser cette fonction VBA :

Function Lettres$(x$)
Dim accent$, y$, i%, z$
accent = "àáâãäåòóôõöøèéêëìíîïùúûüÿñç"
y = LCase(x) 'minuscules
For i = 1 To Len(x)
    z = Mid(y, i, 1)
    If Asc(z) > 96 And Asc(z) < 123 Or InStr(accent, z) Then Lettres = Lettres & Mid(x, i, 1)
Next
End Function

Le code doit être placé impérativement dans un module standard.

Formule en E4 =Lettres(B4)

A+

 

[Sofiane Boukecha]

Bonjour Sofiane Boukecha, le fil,

Oui mais faire le contraire c'est conserver tout ce qui n'est pas un chiffre (espace, tiret etc).

La formule matricielle est plus longue car on ne peut pas utiliser SIERREUR :

=JOINDRE.TEXTE("";VRAI;SI(ESTERR(-STXT(B4;LIGNE(INDIRECT("1:"&NBCAR(B4)));1));STXT(B4;LIGNE(INDIRECT("1:"&NBCAR(B4)));1);""))

A+

bonjour, @job75 , le fil;

merci beaucoup, ça résout mon problème complètement 🙂
merci encore
bonne journée

 

[Sofiane Boukecha]

Bonjour le fil

Comme je n'ai pas Excel 2019 chez moi (mais 2013), je ne dispose pas de JOINDRE.TEXTE
Alors toujours en utilisant FILTRE.XML
(je suis parti du dernier fichier déposé par job75)
Regarde la pièce jointe 1133960
NB: Pour reproduire le test, saisir la formule ci-dessous en E4

=SIERREUR(INDEX(TRANSPOSE(FILTRE.XML("<t><s>"&SUBSTITUE($B4;"-";"</s><s>")&"</s></t>";"//s[translate(.,'1234567890','')=.]"));1;COLONNE()-4);"")

Puis tout en restant dans la barre de formule, sélectionnez F4 et G4 puis valider par CRTL+SHIFT+ENTREE

Sinon lancer cette petite macro qui fera le boulot

Sub Insertion_Formule()
Range("E4:G4").FormulaArray = _
"=IFERROR(INDEX(TRANSPOSE(FILTERXML(""<t><s>""&SUBSTITUTE(B4,""-"",""</s><s>"")&""</s></t>"",""//s[translate(.,'1234567890','')=.]"")),1,COLUMN()-4),"""")"
Range("E4:G6").FillDown
End Sub

bonjour,
merci pour votre retour, mais le résultat est vide avec votre formule
bonne journée

 

[Staple1600]

Bonsoir le fil

Sofiane
La copie d'écran du message#21 montre bien qu'il n'y a pas de vide.
J'ai même ajouté une petite macro pour mettre la formule (au cas où tu oublies de la valider matriciellement)
NB: Ma proposition est juste pour ceux qui s'intéressent à ce qu'on peut faire avec FILTRE.XML.
(Ce n'est une solution pas clé en mains, juste une énième façon de n'extraire que les lettres d'une chaine de caractères que j'ai posté à titre informatif)

 

[Staple1600]

Re

Si on peut utiliser une fonction VBA personnalisée pour ne garder que les lettres
Voici une autre fonction possible
A mettre dans un module standard

Function alpha(txt As String) As String
Dim b, bytes() As Byte: bytes = txt
For Each b In bytes
If Chr(b) Like "[A-Za-z]" Then alpha = alpha & Chr(b)
Next b
End Function

NB: code retrouvé dans mes archives.
Mode d'emploi sur le fichier exemple
=alpha(E4)

Si tu testes sur un fichier vierge (dans lequel tu auras insérer un module puis copier/coller le code VBA dans celui-ci)
Saisis par exemple en A1: Staple1600 a mangé 3 pommes
Puis en B1: =alpha(A1)
Le résultat sera : Stapleamangpommes
Tu vois donc le petit souci qu'heureusement n'a pas la fonction VBA de job75.

EDITION: Une autre version VBA (qui utilise les expressions régulières) (et qui cette fois, conserve les accents)
(toujours issue de mes archives)

Function alphac(S As String) As String
Static RX As Object
If RX Is Nothing Then
Set RX = CreateObject("VBScript.RegExp")
RX.Global = -1
End If
RX.Pattern = "[^a-zA-Zàéèùäë]"
alphac = RX.Replace(S, "")
End Function

NB: si tu veux conserver plus de caractères accentués ajoute-les sur cette ligne
RX.Pattern = "[^a-zA-Zàéèùäë]"
Tu peux t'inspirer de la liste figurant dans le code de job75

 

[Sofiane Boukecha]

Re

Si on peut utiliser une fonction VBA personnalisée pour ne garder que les lettres
Voici une autre fonction possible
A mettre dans un module standard

Function alpha(txt As String) As String
Dim b, bytes() As Byte: bytes = txt
For Each b In bytes
If Chr(b) Like "[A-Za-z]" Then alpha = alpha & Chr(b)
Next b
End Function

NB: code retrouvé dans mes archives.
Mode d'emploi sur le fichier exemple
=alpha(E4)

Si tu testes sur un fichier vierge (dans lequel tu auras insérer un module puis copier/coller le code VBA dans celui-ci)
Saisis par exemple en A1: Staple1600 a mangé 3 pommes
Puis en B1: =alpha(A1)
Le résultat sera : Stapleamangpommes
Tu vois donc le petit souci qu'heureusement n'a pas la fonction VBA de job75.

EDITION: Une autre version VBA (qui utilise les expressions régulières) (et qui cette fois, conserve les accents)
(toujours issue de mes archives)

Function alphac(S As String) As String
Static RX As Object
If RX Is Nothing Then
Set RX = CreateObject("VBScript.RegExp")
RX.Global = -1
End If
RX.Pattern = "[^a-zA-Zàéèùäë]"
alphac = RX.Replace(S, "")
End Function

NB: si tu veux conserver plus de caractères accentués ajoute-les sur cette ligne
RX.Pattern = "[^a-zA-Zàéèùäë]"
Tu peux t'inspirer de la liste figurant dans le code de job75

bonjour,
merci beaucoup pour votre retour.
maintenant mon problème est résolu.

je vous remercie tous.
bonne journée

 

[CHRIS1945]

Bonjour,
Un peu tard mais je vous propose une solution avec une petite fonction à insérer dans un module VBA
Après, il suffit d'appeler la fonction et de lui désigner la cellule contenant le texte original.
L'intérêt est que cela fonctionne quelque soit l'Excel utilisé mais il faut que le classeur soit enregistré pour accepter les macros.
Si compléments d'informations nécessaire, n'hésitez pas à me revenir
Bon courage