XL 2021 Formules que je n'arrive pas à faire

Usine à gaz · 22 Septembre 2023

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

job75 · 22 Septembre 2023

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+

Phil69970 · 22 Septembre 2023

Bonjour Lionel

Données convertir

Et voilou....

StagExcelle · 22 Septembre 2023

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)

Usine à gaz · 22 Septembre 2023

Bjr
Merci pour vos retours.

J'y suis arrivé en passant aussi par plusieurs étapes.
Je joins le fichier avec les formules

laurent950 · 22 Septembre 2023

Usine à gaz à dit:
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

Usine à gaz · 22 Septembre 2023

Bonjour laurent950

Merci pour ton code.

Je vois ça demain et je te dirai.
Encore merci

job75 · 22 Septembre 2023

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+

Usine à gaz · 22 Septembre 2023

job75 à dit:
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

Usine à gaz · 22 Septembre 2023

Re-Bonjour @laurent950

J'ai testé et le résultat :
Col du code : OK exemple : 52120
Col du Nom : Pas OK exemple : " aizanville"
Merci quand même.
Fichier avec code joint

job75 · 22 Septembre 2023

On peut alléger légèrement la formule en D2 avec la fonction REPT :

Code:

=SUPPRESPACE(JOINDRE.TEXTE("";VRAI;SIERREUR(REPT(STXT(B2;6+LIGNE($1:$99);1);(CODE(STXT(B2;6+LIGNE($1:$99);1))<>160)*(CODE(STXT(B2;6+LIGNE($1:$99);1))<>63));"")))

Staple1600 · 22 Septembre 2023

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)

job75 · 23 Septembre 2023

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+

Staple1600 · 23 Septembre 2023

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

Usine à gaz · 23 Septembre 2023

Staple1600 à dit:

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

Usine à gaz · 23 Septembre 2023

job75 à dit:
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,

XL 2021 Formules que je n'arrive pas à faire

Usine à gaz

XLDnaute Barbatruc

Pièces jointes

job75

Phil69970

XLDnaute Barbatruc

StagExcelle

XLDnaute Junior

Usine à gaz

XLDnaute Barbatruc

Pièces jointes

laurent950

XLDnaute Barbatruc

Usine à gaz

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

Pièces jointes

Usine à gaz

XLDnaute Barbatruc

Usine à gaz

XLDnaute Barbatruc

Pièces jointes

job75

XLDnaute Barbatruc

Pièces jointes

Staple1600

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

Pièces jointes

Staple1600

XLDnaute Barbatruc

Usine à gaz

XLDnaute Barbatruc

Usine à gaz

XLDnaute Barbatruc

Discussions similaires