XL 2016 Formule pour récupérer un code postal

[vivi21]

Bonjour,

J'ai une cellule comportant l'adresse complète d'une structure et je souhaiterais récupérer le code postal qui se trouve dans la cellule.
Voici quelques exemple de certaines cellules :

15 RUE DE LA CHARME
21800 CHATILLON EN BAZOIS

ou autre exemple :
3 RUE DE LA SAGESSE
21450 EPOIS

 

[kiki29]

Salut, parmi une myriade voir ici ?

 

[vivi21]

Merci kiki29, je m'aperçois que dans la colonne je n'ai pas d'espace entre le nom de la rue et le code postal donc cela va être difficile de récupérer mon CP.
Bonne journée,
Vivi

 

[kiki29]

Re, une version VBA à tester plus à fond ... à toi de voir, il y a peut-être plus simple (?) via RegExp.

Sub Test_cp_03()
Dim cpt As Long
Dim bTrouve As Boolean
Dim sStr As String, sCh As String
Dim LastRow As Long, i As Long

    LastRow = Feuil1.Range("A" & Rows.Count).End(xlUp).Row

    bTrouve = False
    sCh = ""

    For i = 1 To LastRow
        sStr = Feuil1.Range("A" & i).Text
        For cpt = 1 To Len(sStr)
            Select Case Mid(sStr, cpt, 1)
            Case "0" To "9"
                bTrouve = True
                sCh = sCh & Mid(sStr, cpt, 1)
            Case " ", Chr(13), Chr(10):
                If bTrouve Then
                    If Len(sCh) = 5 Then
                        Feuil1.Range("B" & i).Value = Mid(sStr, cpt - 5, 5)
                    End If
                    bTrouve = False
                    sCh = ""
                End If
            End Select
        Next cpt
    Next i
End Sub

 

[Staple1600]

Bonsoir le fil

Puisque kiki29 évoque regexp
Une fonction personnalisée (à utiliser comme une formule)

Function X_CP(Chaine$) As String
    With CreateObject("vbscript.regexp")
        .Pattern = "\d{5}": X_CP = .Execute(Chaine)(0)
    End With
End Function

Si en A1
15 RUE DE LA CHARME 21800 CHATILLON EN BAZOIS
Cette formule en B1 renvoie 21800
=X_CP(A1)

 

[mapomme]

Bonjour à tous ,

Un essai par formule à compter d'Excel 2016 car il faut disposer de la fonction : Joindre.Texte().
La formule en B2 est matricielle et s'écrit :

=TEXTE(JOINDRE.TEXTE("";VRAI;SI(ESTNUM(--STXT(SUBSTITUE(SUBSTITUE(A2;CAR(10);"");" ";"");LIGNE(INDIRECT("1:"&NBCAR(SUBSTITUE(SUBSTITUE(A2;CAR(10);"");" ";"x"))));5));--STXT(SUBSTITUE(SUBSTITUE(A2;CAR(10);"");" ";"");LIGNE(INDIRECT("1:"&NBCAR(SUBSTITUE(SUBSTITUE(A2;CAR(10);"");" ";"x"))));5);""));"00000")

Elle est basée sur le fait qu'il n'y aura dans l'adresse qu'un seul nombre à 5 chiffres qui sera donc le code postal. Le code postal peut-être situé n'importe où dans l'adresse.

 

[mapomme]

Re,

Pour le fun, en bon VBAiste, une fonction purement VBA :

Function extCP(ByVal xcp) As String       ' mapomme
Dim i&
   For i = Len(xcp) - 4 To 1 Step -1
      If Mid(xcp, i, 5) Like "#####" Then extCP = Mid(xcp, i, 5): Exit Function
   Next i
End Function

Mêmes résultats que la fonction de Staple1600 (que je salue ).

 

[Staple1600]

Re

Pour le fun, en bon flâneur sur les forums anglophones , et pour saluer mapomme
Une formule (qui fonctionne sur 365)
=STXT(A1;TROUVE("0111110";0&CONCAT(--(ESTNUM(-STXT(A1;SEQUENCE(NBCAR(A1));1))))&"0111110");5)

Pas de validation matricielle.