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

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

Solution
Bonjour à tous,

Depuis Excel 2016 il existe la fonction JOINDRE.TEXTE.

Voyez le fichier joint et cette formule matricielle en E4 :
Code:
=JOINDRE.TEXTE("";VRAI;SIERREUR(--STXT(B4;LIGNE(INDIRECT("1:"&NBCAR(B4)));1);""))
à valider par Ctrl+Maj+Entrée.

A+
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
 
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:
VB:
=DROITE(SUBSTITUE([@ID];" ";REPT(" ";250));250)

Si on veut le"nombre", on peut utiliser:
VB:
=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
 
Bonjour à tous,

Depuis Excel 2016 il existe la fonction JOINDRE.TEXTE.

Voyez le fichier joint et cette formule matricielle en E4 :
Code:
=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
 
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 :
Code:
=JOINDRE.TEXTE("";VRAI;SI(ESTERR(-STXT(B4;LIGNE(INDIRECT("1:"&NBCAR(B4)));1));STXT(B4;LIGNE(INDIRECT("1:"&NBCAR(B4)));1);""))
A+
 

Pièces jointes

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)
exFILXML.png

NB: Pour reproduire le test, saisir la formule ci-dessous en E4
Code:
=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
VB:
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 Sofiane Boukecha, le fil,

Si l'on veut ne garder que les lettres, avec ou sans accents, on peut utiliser cette fonction VBA :
VB:
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+
 

Pièces jointes

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 :
Code:
=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
 
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
Code:
=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
VB:
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
 
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)
 
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
VB:
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)
VB:
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
 
Dernière édition:
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
VB:
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)
VB:
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
 
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
 

Pièces jointes

- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD

Discussions similaires

Réponses
3
Affichages
385
  • Question Question
XL 2021 Doublons
Réponses
7
Affichages
65
Réponses
4
Affichages
102
Réponses
11
Affichages
568
Retour