XL 2021 Formules que je n'arrive pas à faire

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 !

Usine à gaz

XLDnaute Barbatruc
Supporter XLD
Bonjour à toutes et à tous 🙂

Malgré toutes mes tentatives et mes recherches, je n'y suis pas arrivé.

Dans le fichier Excel joint, à partir d'un mail, j'ai copié des codes postaux avec noms des communes.
Le collage s'affiche dans une seule colonne (B) comme ceci :
- de la ligne 02 à la ligne 092 : "52 120      aizanville"
- de la ligne 93 à la ligne130 : "10200 lignol le chateau"

J'ai besoin de mettre ces valeurs sur 2 colonnes (C et D) comme ceci :
Colonne C : les codes postaux,
Colonne D : les noms des communes,

Si vous aviez la solution, ça m'arrangerait bien 🙂

En cas, je joins le fichier...
Un grand MERCI à tous
🙂
 

Pièces jointes

Solution
Bonjour Lionel, Phil69970, StagExcelle, laurent950,

Formule en C2, à tirer vers le bas :
Code:
=SUBSTITUE(SUBSTITUE(GAUCHE(B2;6);" ";);CAR(160);)
Formule matricielle en D2, à tirer vers le bas :
Code:
=SUPPRESPACE(JOINDRE.TEXTE("";VRAI;SIERREUR(SI((CODE(STXT(B2;6+LIGNE($1:$99);1))<>160)*(CODE(STXT(B2;6+LIGNE($1:$99);1))<>63);STXT(B2;6+LIGNE($1:$99);1);"");"")))
JOINDRE.TEXTE existe à partir d'Excel 2019.

Sur Excel 2021 la validation matricielle n'est pas nécessaire.

A+
Bonjour Lionel


Données convertir
1695377200850.png

Et voilou....
 
Bonjour

Je suis passé par deux étapes
avec une macro
Sub Macro()
Columns("B:B").Replace What:=ChrW(8194), Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

et après une formule (je l'ai installé dans la colonne E)
=SUPPRESPACE(B2)

Après, il faut une troisième formule
(pour le code postal)
=GAUCHE(E2;6)
et la dernière pour la ville
=STXT(E2;NBCAR(F2)+2;1000)
 
J'y suis arrivé en passant aussi par plusieurs étapes.
Bonjour @Usine à gaz Lionel une seule étapes, si cela peut te servir.

VB:
Sub DecoupageCodesPostauxNomsDesCommunes()
Dim Rng As Range
Dim Res As Variant
    Set Rng = Range(Cells(2, 2), Cells(Cells(1048576, 2).End(xlUp).Row, 2))
    ' Si il y a une cellule vide, Gestion des erreurs
    On Error Resume Next
    For Each Res In Rng
        Res.Offset(, 3) = Val(Replace(Res.Value, " ", ""))
        Res.Offset(, 4) = Right(Replace(Res.Value, " ", ""), Len(Replace(Res.Value, " ", "")) - Len(CStr(Val(Replace(Res.Value, " ", "")))))
    Next Res
    On Error GoTo 0
End Sub
 
Dernière édition:
Bonjour Lionel, Phil69970, StagExcelle, laurent950,

Formule en C2, à tirer vers le bas :
Code:
=SUBSTITUE(SUBSTITUE(GAUCHE(B2;6);" ";);CAR(160);)
Formule matricielle en D2, à tirer vers le bas :
Code:
=SUPPRESPACE(JOINDRE.TEXTE("";VRAI;SIERREUR(SI((CODE(STXT(B2;6+LIGNE($1:$99);1))<>160)*(CODE(STXT(B2;6+LIGNE($1:$99);1))<>63);STXT(B2;6+LIGNE($1:$99);1);"");"")))
JOINDRE.TEXTE existe à partir d'Excel 2019.

Sur Excel 2021 la validation matricielle n'est pas nécessaire.

A+
 

Pièces jointes

Bonjour Lionel, Phil69970, StagExcelle, laurent950,

Formule en C2, à tirer vers le bas :
Code:
=SUBSTITUE(SUBSTITUE(GAUCHE(B2;6);" ";);CAR(160);)
Formule matricielle en D2, à tirer vers le bas :
Code:
=SUPPRESPACE(JOINDRE.TEXTE("";VRAI;SIERREUR(SI((CODE(STXT(B2;6+LIGNE($1:$99);1))<>160)*(CODE(STXT(B2;6+LIGNE($1:$99);1))<>63);STXT(B2;6+LIGNE($1:$99);1);"");"")))
JOINDRE.TEXTE existe à partir d'Excel 2019.

Sur Excel 2021 la validation matricielle n'est pas nécessaire.

A+
Bjr Gérard 🙂
Merci encore d'être là.
Nickel comme d'hab...
Mercin
🙂
 
Bonsoir @Usine à gaz , le fil

Deux formules de "nettoyage" (*)
• Pour XL 2021
=SUPPRESPACE(CONCAT(SI(ESTNUM(CHERCHE(STXT(B2;LIGNE(B$1:INDEX(B:B;NBCAR(B2)));1);" 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"));STXT(B2;LIGNE(B$1:INDEX(B:B;NBCAR(B2)));1);" ")))
• Avec O365, une formule plus courte
=LET(²;STXT(B2;SEQUENCE(NBCAR(B2));1);SUPPRESPACE(CONCAT(SI(ESTNUM(CHERCHE(²;" 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"));²;" "))))
Ensuite on fait dans le classique avec SUBSTITUE, GAUCHE et DROITE
Et là tu devrais savoir faire, Lionel 😉

(*) glanées sur le web anglophone

NB: Pour XL 2021, je crois que la validation doit être matricielle
(ce qui n'est pas le cas avec O365)
 
Bonjour Lionel, JM, le forum,

Pour éliminer CAR(160) il suffit de le faire avec SUBSTITUE, c'est plus simple :
Code:
=SUPPRESPACE(SUBSTITUE(JOINDRE.TEXTE("";VRAI;SIERREUR(REPT(STXT(B2;6+LIGNE($1:$99);1);CODE(STXT(B2;6+LIGNE($1:$99);1))<>63);""));CAR(160);" "))
Il y a 2 ou 3 CAR(160) à partir de la ligne 93.

PS : Lionel faut-il la validation matricielle par Ctrl+Maj+Entrée sur ton Excel 2021 ?

A+
 

Pièces jointes

Re, Bonjour job75

Pour les fainéants, ou ceux qui ont les doigts fragiles
(ou pour varier les plaisirs)
VB:
Sub Traitement()
Dim D_L&, P As Range
D_L = Cells(Rows.Count, "B").End(xlUp).Row
Set P = Range("A2:A" & D_L)
P.Formula2 = "=TRIM(Nettoyer(B2))": P = P.Value
P.TextToColumns Destination:=Range("C2"), DataType:=2, FieldInfo:=Array(Array(0, 1), Array(6, 1))
Range("C2:C" & D_L).NumberFormat = "General"
Columns(1).ClearContents
End Sub
Private Function Nettoyer(str As String, Optional vPt As String = "[^a-zA-Z0-9]") As String
With CreateObject("vbscript.regexp")
.Global = -1: .Pattern = vPt
Nettoyer = .Replace(str, Chr(32))
End With
End Function
 
Re, Bonjour job75

Pour les fainéants, ou ceux qui ont les doigts fragiles
(ou pour varier les plaisirs)
VB:
Sub Traitement()
Dim D_L&, P As Range
D_L = Cells(Rows.Count, "B").End(xlUp).Row
Set P = Range("A2:A" & D_L)
P.Formula2 = "=TRIM(Nettoyer(B2))": P = P.Value
P.TextToColumns Destination:=Range("C2"), DataType:=2, FieldInfo:=Array(Array(0, 1), Array(6, 1))
Range("C2:C" & D_L).NumberFormat = "General"
Columns(1).ClearContents
End Sub
Private Function Nettoyer(str As String, Optional vPt As String = "[^a-zA-Z0-9]") As String
With CreateObject("vbscript.regexp")
.Global = -1: .Pattern = vPt
Nettoyer = .Replace(str, Chr(32))
End With
End Function
Bonjour JM, le Fil, le Forum 🙂
Merci JM, c'est nickel
🙂
 
Bonjour Lionel, JM, le forum,

Pour éliminer CAR(160) il suffit de le faire avec SUBSTITUE, c'est plus simple :
Code:
=SUPPRESPACE(SUBSTITUE(JOINDRE.TEXTE("";VRAI;SIERREUR(REPT(STXT(B2;6+LIGNE($1:$99);1);CODE(STXT(B2;6+LIGNE($1:$99);1))<>63);""));CAR(160);" "))
Il y a 2 ou 3 CAR(160) à partir de la ligne 93.

PS : Lionel faut-il la validation matricielle par Ctrl+Maj+Entrée sur ton Excel 2021 ?

A+
Bjr Gérard 🙂
Encore nickel,
Merci beaucoup,
🙂
 
- 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

Retour